- Is adding index on rapidly changing column like "lastUpdatedOn" worth it ?
- How to calculate tradeoff ?
- Can someone point me to official documentation on when and how does MySQL reindexes on row inserts and updates on indexed column.
0
Whether to have an index including a "rapidly changing column" is a tradeoff.
An UPDATE needs to delete one entry in an the index and add a new entry elsewhere in the index.
On the other hand, the index may greatly speed up due to the index.
Please provide a concrete example so we can discuss the tradeoffs further.
Regular non-UNIQUE indexes (as opposed to FULLTEXT and SPATIAL) are maintained thus:
There is a "change buffer" (qv) in the buffer_pool that maintains index updates that have not yet been written to disk.
When a DELETE occurs, an entry is added to the Change Buffer to say that the index entry needs to be removed.
For UPDATE two entries may need to be put into the CB.
When a SELECT uses such an index, it checks both the CB and the real, on-disk, BTree for the index. That BTree is cached (block by block) in the buffer_pool. (A block is 16KB and may hold hundred(s) of entries.)
The CB is flushed to disk "in the background" or "as needed". That involves fetching an index block (unless already cached), updating some entries (delete, and/or add), and write back to disk. Both the read and write are cached in the buffer_pool, so either or neither may be a physical I/O.
MySQL does not "rebuild" a regular index ("reindex") except via certain ALTERs or OPTIMIZE. That is, all changes are made on the fly. The action of the CB is transparent to the user.