Comparing queries with mk-query-profiler and diff

EXPLAIN is not the only way to analyze query perfomance im MySql because some things are not being taken into account. For example LIMIT clauses or the cost of the optimizer. There is also the mk-query-profiler.

An interesting way to compare the performance of two queries is to use mk-query-profiler along with diff

Here’s how you do it. As an example I take the queries from this mysql performance blog article article. Because I’d like to learn what excactly SQL_CALC_FOUND_ROWS does.

1. Profile the two queries and write the results into two files

# echo "SELECT SQL_NO_CACHE * FROM count_test \
WHERE b = 555 ORDER BY c LIMIT 5;" \
 | mk-query-profiler -vv --database test > result_query2.txt

# echo "SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS \
* FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;" \
 | mk-query-profiler -vv --database test > result_query1.txt

2. Compare the results with diff

# diff --side-by-side result_query1.txt result_query2.txt

Here’s the result

3. Interpret the result.

The differences are highlighted by a vertical bar in the middle column:

In this case: SQL_CALC_FOUND_ROWS causes 77 more “next in index” operations reads causing 19 more memory accesses. However no disk accesses which is nice.

Of course this is a very simple case. But you gotta start simple to understand the more complicated stuff.