Is query cost the best metric for MySQL query optimization?

Question

I am in the process of optimizing the queries in my MySQL database. While using Visual Explain and looking at various query costs, I'm repeatedly finding counter-intuitive values. Operations which use more efficient lookups (e.g. key lookup) seem to have a higher query cost than ostensibly less efficient operations (e.g full table scan or full index scan).

Examples of this can even be seen in the MySQL manual, in the section regarding Visual Explain on this page: The query cost for the full table scan is a fraction of the key-lookup-based query costs. I see exactly the same scenario in my own database.

All this seems perfectly backwards to me, and raises this question: should I use query cost as the standard when optimizing a query? Or have I fundamentally misunderstood query cost?

Answer 1

MySQL does not have very good metrics relating to Optimization. One of the better ones is EXPLAIN FORMAT=JSON SELECT ..., but it is somewhat cryptic.

Some 'serious' flaws:

• Rarely does anything account for a LIMIT.
• Statistics on indexes are crude and do not allow for uneven distribution. (Histograms are coming 'soon'.)
• Very little is done about whether data/indexes are currently cached, and nothing about whether you have a spinning drive or SSD.

I like this because it lets me compare two formulations/indexes/etc even for small tables where timing is next to useless:

FLUSH STATUS;
perform the query
SHOW SESSION STATUS LIKE "Handler%";

It provides exact counts (unlike EXPLAIN) of reads, writes (to temp table), etc. Its main flaw is in not differentiating how long a read/write took (due to caching, index lookup, etc). However, it is often very good at pointing out whether a query did a table/index scan versus lookup versus multiple scans.

The regular EXPLAIN fails to point out multiple sorts, such as might happen with GROUP BY and ORDER BY. And "Using filesort" does not necessarily mean anything is written to disk.