is it possible add index to a column in database from rails itself ? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 26, 2007, at 4:49 AM, Pokkai Dokkai wrote:> > is it possible add index to a column in database from rails itself ?In a migration, add_index :table, :column Peace, Phillip --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Phillip Koebbe wrote:> On Dec 26, 2007, at 4:49 AM, Pokkai Dokkai wrote: > >> >> is it possible add index to a column in database from rails itself ? > > In a migration, > > add_index :table, :column > > Peace, > Phillipfor this i need to run migration file i want to do it in controller ( during dynamic ) like User.add_index(:phone_no) is there any idea? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
You can''t do it DURING a controller call. You either do it before, like Phillip suggested, or never at all. On Dec 27, 2007 4:19 PM, Pokkai Dokkai <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Phillip Koebbe wrote: > > On Dec 26, 2007, at 4:49 AM, Pokkai Dokkai wrote: > > > >> > >> is it possible add index to a column in database from rails itself ? > > > > In a migration, > > > > add_index :table, :column > > > > Peace, > > Phillip > > for this i need to run migration file > i want to do it in controller ( during dynamic ) > like User.add_index(:phone_no) > > is there any idea? > -- > Posted via http://www.ruby-forum.com/. > > > >-- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Refer awdr book, it has an example that shows dynamically adding index. Sent from my iPhone On Dec 26, 2007, at 9:49 PM, Pokkai Dokkai <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org > wrote:> > Phillip Koebbe wrote: >> On Dec 26, 2007, at 4:49 AM, Pokkai Dokkai wrote: >> >>> >>> is it possible add index to a column in database from rails itself ? >> >> In a migration, >> >> add_index :table, :column >> >> Peace, >> Phillip > > for this i need to run migration file > i want to do it in controller ( during dynamic ) > like User.add_index(:phone_no) > > is there any idea? > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Bcp wrote:> Refer awdr book, it has an example that shows dynamically adding index. > > Sent from my iPhone > > On Dec 26, 2007, at 9:49 PM, Pokkai Dokkai > <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.orgthanks for the help where you sent it ? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 26, 2007, at 11:50 PM, Ryan Bigg wrote:> You can''t do it DURING a controller call. You either do it before, > like Phillip suggested, or never at all. >Hm. Not sure that "never at all" is completely accurate. You can run arbitrary SQL statements against your database, but the better question would be "is that the best design?" I suppose that if you have an application that is dynamically adding columns to tables, and some/all of those columns need to be indexed, then yes, you''d need to figure out how to add indexes on the fly. But I would think that if you''re adding columns on the fly, then you should already know about running arbitrary SQL. OP, may I ask *why* you need to define an index in the controller? Peace, Phillip --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
A little late to the discussion but are you sure you want to add indexes on the fly and not just use :order? It makes more sense IMO to add indexes in the migrations and just order on the already indexed fields. But I''m just making guesses at what yr actually trying to do. RSL On Dec 27, 2007 7:30 AM, Phillip Koebbe <phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > On Dec 26, 2007, at 11:50 PM, Ryan Bigg wrote: > > > You can''t do it DURING a controller call. You either do it before, > > like Phillip suggested, or never at all. > > > > Hm. Not sure that "never at all" is completely accurate. You can > run arbitrary SQL statements against your database, but the better > question would be "is that the best design?" I suppose that if you > have an application that is dynamically adding columns to tables, and > some/all of those columns need to be indexed, then yes, you''d need to > figure out how to add indexes on the fly. But I would think that if > you''re adding columns on the fly, then you should already know about > running arbitrary SQL. > > OP, may I ask *why* you need to define an index in the controller? > > Peace, > Phillip > > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
RSL ___ wrote:> A little late to the discussion but are you sure you want to add > indexes on the fly and not just use :order? It makes more sense IMO to > add indexes in the migrations and just order on the already indexed > fields. But I''m just making guesses at what yr actually trying to do. > > RSLi wrote some join query it will take lot of time to fetch the data .after putting index to those corresponding column ,my query fetsh the data very fast . so i want to add index dynamically to some particular column , then after my query executing i should remove the indexes because more indexing will increase the fetching time (because i studied in a book) any idea ? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 2007, at 8:48 AM, Pokkai Dokkai wrote:> > RSL ___ wrote: >> A little late to the discussion but are you sure you want to add >> indexes on the fly and not just use :order? It makes more sense >> IMO to >> add indexes in the migrations and just order on the already indexed >> fields. But I''m just making guesses at what yr actually trying to do. >> >> RSL > > i wrote some join query it will take lot of time to fetch the data > .after putting index to those corresponding column ,my query fetsh the > data very fast . > so i want to add index dynamically to some particular column , > then after my query executing i should remove the indexes > because more indexing will increase the fetching time (because i > studied > in a book) >Adding and removing the index dynamically will have overhead as well. My opinion is to add indexes to columns that will be join targets and use in where clauses. You can actually have too many indexes in a database, but you have to be index happy to get to that point. If you are legitimately querying data in such a way that the index will be beneficial, create it properly and leave it there. Don''t create it, drop it, create it, drop it, create it, drop it. That''s just silly. Besides, a database engine has something called a query optimizer. Having assorted indexes allows the optimizer to choose how best to go about fetching your data. Now, that''s not to say that all optimizers work as well as others, or even work at all, but generally they are good things. Having indexes gives the optimizer options. Peace, Phillip --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > thanks for the help > where you sent it ?He didn''t actually send you the book, he just likes bragging that he can post from his iPhone. -- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 6:48 am, Pokkai Dokkai <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > i wrote some join query it will take lot of time to fetch the data > .after putting index to those corresponding column ,my query fetsh the > data very fast .I''m surprised that you''d create indexes on the fly for the join class - I''d expect it for the where clause, though. In which case, feel free to index on every and all columns involved in the queries you receive, if it makes things faster. The main drawback with indexes is that they make inserting records slower. If your data is read many more times than it''s written (which is fairly common), then indexing the heck out of the table may make sense. Keep in mind that the main purpose of indexes is to reduce the number of rows in a table that have to be read to answer a query. If you create an index each time you query, you''re automatically reading all the rows all the time. A one-query index is not a good idea. ///ark --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 6:21 pm, Mark Wilden <m...-OCn100epQuBBDgjK7y7TUQ@public.gmane.org> wrote:> > I''m surprised that you''d create indexes on the fly for the join classThat''s "join clause," of course. Sorry - normally I wouldn''t bother with the correction, but in this case, it might be confusing. ///ark --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 2007, at 9:21 PM, Mark Wilden wrote:> > On Dec 27, 6:48 am, Pokkai Dokkai <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: >> >> i wrote some join query it will take lot of time to fetch the data >> .after putting index to those corresponding column ,my query fetsh >> the >> data very fast . > > I''m surprised that you''d create indexes on the fly for the join clause > - I''d expect it for the where clause, though. In which case, feel free > to index on every and all columns involved in the queries you receive, > if it makes things faster. The main drawback with indexes is that they > make inserting records slower. If your data is read many more times > than it''s written (which is fairly common), then indexing the heck out > of the table may make sense. > > Keep in mind that the main purpose of indexes is to reduce the number > of rows in a table that have to be read to answer a query. If you > create an index each time you query, you''re automatically reading all > the rows all the time. A one-query index is not a good idea. > > ///arkWhile I think the "automatically reading all the rows all the time" is a bit of an exaggeration since the database engine is going to build an index more efficiently that reading all the rows the way you think of them in an ActiveRecord sense, the point is basically true. However, I have run into a situation during a database migration involving some initialization of new columns that was tremendously faster when creating a particular index at the beginning, doing the requisite data wrangling, and dropping the index at the end than just doing the exact same data wrangling on the schema without the index. I''ll grant that the index was used very many times before being dropped, but it wouldn''t have served a useful purpose outside the particular migration. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 7:58 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> > While I think the "automatically reading all the rows all the time" is > a bit of an exaggeration since the database engine is going to build > an index more efficiently that reading all the rows the way you think > of them in an ActiveRecord sense, the point is basically true.What I meant was that to create an index, the database has to read every row of the indexed table. There''s no way around that. Since one point of indexes is to avoid reading every row, creating an index can''t speed up a single query. (That said, it''s entirely possible for the query optimizer to create its own indexes, but you can bet it knows what it''s doing in that case.)> However, I have run into a situation during a database migration > involving some initialization of new columns that was tremendously > faster when creating a particular index at the beginning, doing the > requisite data wrangling, and dropping the index at the end than just > doing the exact same data wrangling on the schema without the index. > I''ll grant that the index was used very many times before being > dropped, but it wouldn''t have served a useful purpose outside the > particular migration.Definitely sounds like a good use of a one-use (if not one-query) index. I''ve created temporary indexes in stored procedures, myself. Almost always, however, they would be used in iterative, or even cursor-based, situations. ///ark --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---