In a migration,
add_index :table, :column
Peace, Phillip
Refer awdr book, it has an example that shows dynamically adding index.
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
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
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
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
That's "join clause," of course.
Sorry - normally I wouldn't bother with the correction, but in this case, it might be confusing.
///ark
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.
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@AgileConsultingLLC.com
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
