When does MySQL update the indexes

Question

When exactly are the indexes updated after an update/insert? Is it before the update/insert query returns, is it sometime after after the query returns, or is it when a query using the indexes is executed.

Answer 1

Indexes updates are two-fold.

The first part is inserting/updating/deleting the entry into/from the index. The index gets updated as soon as a record is changed, and this process blocks the query until completion.

This allows faster retrieval of records based on an a condition on an indexed column, the most well-understood purpose of an index.

The second part is updating statistics of the index. This allows the optimiser to determine if for a given query it is even worth using the index. Imagine a query like SELECT * FROM users WHERE disabled = 0. Assume most users are in fact active. If the index statistics are up-to-date, the optimiser will realise that most records from the table will be returned by the query, the table will almost entirely need to be scanned. It will likely decide not to use the index and scan the table straight away.

This update does not take place automatically except in very specific situations. These statistics should be manually updated on a regular basis with an ANALYZE TABLE [table_name]

Answer 2

You may want to refer to the following links which I found helpful for your question:

1. This question on stackoverflow
2. This link from mysqlperformanceblog
3. This post from DBA Stackexchange forum