How to find out what MySQL is actually doing

Explain is a great tool for looking into the strategy chosen by the query optimizer for executing the query but you have to bear in mind that it is only an estimate. Explain can dynamically sample the data to decide what the best execution plan is or in some cases it can actually use static statistics. This means that occasionally a query that looks great when using explain can actually perform very badly. Fortunately finding out what MySQL actually did when it executed a query is surprisingly easy as MySQL keeps a huge amount of session information which is accesible throughout the session. We can access this data by running SHOW SESSION STATUS;

In terms of seeing how the query was actually executed we only really care about the variables relating to the handler. This performs the low level interactions with the DB engine, issuing requests to read rows etc. We can access these session variables using the following line

SHOW SESSION STATUS LIKE 'ha%';

This will show us all the handler variables I've detailed a few interesting ones below.

  • Handler_read_rnd is the number of requests to read a row based on a fixed position. A high value here could indicate you have joins that do not use keys properly.
  • Handler_read_rnd_next shows the number of requests to read the next row in the data file. This value would be high if you are doing a full table scan.
  • Handler_read_key shows the number of requests to read a row based on a key. If this value is high then it's a good indication that your tables are properly indexed.
  • Handler_read_next is the number of requests to read the next row in key order. This would be incremented if you are querying an index with a range constraint or are doing an index scan.
  • Handler_read_first indicates the number of times the first entry in an index was read. A high value suggests that you are doing a lot of full index scans.

Full details of all the handler variables can be found here

If you are investigating a particular query you would want to reset all status variables before you run the query. This can be done as follows.

FLUSH STATUS;

SOME QUERY;

SHOW SESSION STATUS LIKE 'ha%';

This gives you just the handler values for that query. Using this combined with explain can allow you to explore what your query is doing to a greater degree and the right combination of these two can help optimize any queries.