We all know that if we add a MySQL index to speed up a read, we
end up making writes slower. How often do we do the analysis to
look at how much more work is done?
Recently, a developer came to me and wanted to add an index to a
very large table (hundreds of gigabytes) to speed up a query. We
did some testing on a moderately used server:
Set long_query_time to 0 and turn slow query logging on
Turn slow query logging off after 30 minutes.
Add the index (was on a single field)
Repeat the slow query logging for 30 minutes at a similar time
frame (in our case, we did middle of the day usage on a Tuesday
and Wednesday, when the database is heavily used).
Then I looked at the write analysis – there were no DELETEs, no
UPDATEs that updated the indexed field, and no UPDATEs that used
the indexed field in the filtering. There were only INSERTs, and
with the help of pt-query-digest, here’s what I found: …[Read more]