How to add index to a Model.attribute?

is it possible add index to a column in database from rails itself ?

On Dec 26, 2007, at 4:49 AM, Pokkai D. wrote:

is it possible add index to a column in database from rails itself ?

In a migration,

add_index :table, :column

Peace,
Phillip

Phillip K. wrote:

On Dec 26, 2007, at 4:49 AM, Pokkai D. 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?

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 D.
<[email protected]

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 D.
[email protected]
wrote:


Ryan B.

Feel free to add me to MSN and/or GTalk as this email.

On Dec 26, 2007, at 11:50 PM, Ryan B. 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

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

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 D.
<[email protected]

thanks for the help
where you sent it ?

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)

any idea ?

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 B.

Feel free to add me to MSN and/or GTalk as this email.

On Dec 27, 2007, at 8:48 AM, Pokkai D. 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 .
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

On Dec 27, 6:21 pm, Mark W. [email protected] wrote:

I’m surprised that you’d create indexes on the fly for the join class

That’s “join clause,” of course.

Sorry - normally I wouldn’t bother with the correction, but in this
case, it might be confusing.

///ark

On Dec 27, 6:48 am, Pokkai D. [email protected]
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

On Dec 27, 2007, at 9:21 PM, Mark W. wrote:

  • I’d expect it for the where clause, though. In which case, feel free

///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 B. http://agileconsultingllc.com
[email protected]

On Dec 27, 7:58 pm, Rob B. [email protected]
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