While doing a performance audit for a customer a few weeks ago, I tried to improve the response time of their top slow query according to
pt-query-digest‘s report. This query was run very frequently and had very unstable performance: during the time data was collected, response time varied from 50µs to 1s.
When I ran the query myself (a two-table join with a
WHERE condition, the whole dataset was in memory), I always got a consistent response time (about 160ms). Of course, I wanted to know more about how MySQL executes this query. So I used commands you’re probably familiar with:
SHOW STATUS LIKE 'Handler%'.
Handler counters only confirmed that the execution plan seemed reasonable and that fields were correctly indexed.