Printable View
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.
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.
So, if the field to be indexed is not updated much, am I better off filling the table with values and then creating index?
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.
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.
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?
Yes - row counts around a million.
On tables with "several" indexes.
@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.
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.
Then, add your rows and then add your indexes and go from there.
Thanks to all of you.