[RESOLVED] indexes

[srisa]

Hello,
I don't know if we can do this other databases but we can create indexes in mysql at the time of creating table.
What I want to know is are the indexes updated automatically as and when we insert new data or update existing data?
I am quite ignorant about these things, so my question might not make sense.
Thank you.

[si_the_geek]

It might vary for some database systems, but for all of the ones I'm aware of, yes, indexes are updated when data is inserted/updated.

Note however that it means inserting/updating takes longer, as it takes some extra time to also update the index.

[srisa]

So, if the field to be indexed is not updated much, am I better off filling the table with values and then creating index?

[Hack]

More than likely you are always going to be adding/deleteing/updating etc records in your database. Typically I just create the indexs with the table and leave it alone unless an index goes nutso and needs to be dropped and readded.

[szlamany]

There are times we have "dropped" an index so insert's of many, many rows can be done quickly.

Then a "re-create" of the index is done after the inserts.

It's a rare requirement - but definitely one that we have done.

[Hack]

I would image though that the INSERT would have to be pretty massive in order for you to feel the need to do that, right?

[szlamany]

Yes - row counts around a million.

On tables with "several" indexes.

[Hack]

@sirsa: With the kind of inserts that szlamany is talking about, then I would suggest you follow suit and drop/create before/after your INSERTS.

However, most INSERTS don't contain those kind of numbers so typically leaving the indexs alone (as long as they are doing their job) would be a standard way to go.

[srisa]

Thanks for all your replies. The point is I will be filling the table once with about million rows. After that , updates will be made to the other fields other than the one that is indexed. Where clause will be having the indexed field most of the times.

[Hack]

Then, add your rows and then add your indexes and go from there.

[srisa]

Thanks to all of you.