CODEWITHSUNDEEP
sqlmysql
Brian
Brian

Reputation: 27402

Do MySQL indexes still work after inserting new rows?

I have a MySQL table with an index covering two fields. If I insert new rows into the table, will my index still work?

Upvotes: 6

Views: 3240

Answers (5)

David
David

Reputation: 5016

Yes, the index is automatically updated; therefore indices (aka indexes) make inserts, updates and deletes slower.

Upvotes: 11

wallyk
wallyk

Reputation: 57784

As others have stated, indices are updated with each insert. It's part of the performance penalty for inserts and updates.

Actually this isn't as odd a question as some answerers allude. In the 1980s I engineered a system using an RDBMS which deferred updates "for performance" (according to the documentation). Ironically it was named RDM, originally for Realtime Data Manager (or something very close to that), but later backronymed Responsive Data Manager.

RDM seemed intended for humans speed updates, so deferring database writes until several rows were complete was probably acceptable. The system I engineered used the database backend wrapped by a database server connected to automatic data sources. AFAIK, this was the first application of RDM expecting rapid row inserts. One of the problem solutions was engineering a timestamp field so all the nodes in the system could say "update me with everything which has changed since yyyy-mm-dd hh:mm:ss". Turns out that was a worst-case index update/refresh scenario, and hell to diagnose. Once undocumented calls meaning trust me, I really want the data flushed and the indexes updated now were added, it worked pretty well.

Upvotes: 3

ysth
ysth

Reputation: 98398

Yes. Could you explain more why you thought it might not?

Note that if you also have individual indexes on one or both of the fields, mysql (at least some 5.0 versions I've used) won't necessarily use the combined index by default; when in doubt, use EXPLAIN to verify that your indexes are used as you expect.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332691

I have a MySQL table with an index covering two fields. If insert new rows into the table, will my index still work?

Yes, the index is updated every time there is an insert/update/delete executed for that table.

Upvotes: 9

zombat
zombat

Reputation: 94217

Yep, it will still work. Indexes would be pretty useless if you couldn't use them after you had inserted new data. Be aware that the more data you add to your tables, the bigger your indexes will grow, so you want to be sure you create them properly and limit them to what you need them for.

Upvotes: 6

Related Questions