Description:
Hi,
In Bug#104659 and Bug#111570, I present two rows examined blindspots. In this Feature Request, I present a solution for solving these blindspots.
I suggest implementing 2 new counters:
- Random Rows Examined,
- and Sequential Rows Examined.
These counters should be at the global status level (like Innodb_rows_read), at the InnoDB Metric level (like dml_reads), in the slow log (like rows examined), in the Performance Schema table events_statements_summary_by_digest (like SUM_ROWS_EXAMINED) and in the Performance Schema table table_io_waits_summary_by_table (like COUNT_FETCH).
These would work as described below:
- A primary key lookup would increment random rows examined (many lookups in a single query would increment the counter many times),
- A secondary covering index lookup would also increment random rows examined,
Looking at the next element in a range scan on the clustered index or a secondary index would increment sequential rows examined,
- A secondary index lookup leading to a primary key lookup (not index covering) would also increment random rows examined (in addition to incrementing random rows examined if this is the first access to the index, or in addition to incrementing sequential rows examined if this is a range scan on the index),
- The first Index Condition Pushdown (ICP) Storage Engine call would increment the random rows examined counter (like for a covering index lookup), and then would increment the sequential rows examined counter for all the next rows of the index it is scanning to find a condition (potentially zero if the first lookup matches the condition), and if this leads to a condition match (there is also the potential for no match), it would increment the random rows examined counter again,
- The following ICP potentially calls would increment the sequential rows examined counter for all the rows not matching the condition, and if a match is found, the following primary key lookup would increment the random rows examined.
There are probably things I am missing in the implementation of these new counters, feel free to improve the design on implementation or share thoughts in the comments below.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
N/A: this is a feature request.
