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.
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.
# 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
# diff --side-by-side result_query1.txt result_query2.txt
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.