Monday, June 26, 2023

Rows Examined Blindspot when Looking for non-Existing Data

When trying to understand queries in the slow log, an interesting metric to look at is rows examined.  Also, when trying to understand CPU spikes on a MySQL instance, the InnoDB Metric dml_reads and the global status Innodb_rows_read are interesting to check.  In a similar way, when trying to gather information about which queries are loading the system, SUM_ROWS_EXAMINED from the Performance Schema table events_statements_summary_by_digest is interesting to analyze.  But rows examined might not mean what you think, and I give an example in this post.

TL&DR: in MySQL and from what I understand, rows examined means rows returned by the storage engine, even if the name would lead you to think that it is rows read.  A consequence of this is that a query looking for non-existing data has 0 rows examined, and this is what I call in this post a rows examined blindspot.