1

As per MySql documentation link:

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Handler_read_key

Handler_read_key - The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

Can someone please explain / simplify / elaborate this definition.

0

1 Answer 1

2

A value of thousands per second is pretty normal. (Manually compute Handler_read_key / Uptime.)

Do the following to get a better feel for what it, and its siblings, are about:

FLUSH STATUS;  -- resets the Handler counters (and a few other things)
SELECT ...
SHOW SESSION STATUS LIKE 'Handler_%';

You will see read_key for "point queries" and for "range queries", possibly one each. You will also see a larger value for a "range query" in read_next.

Handler_write% values indicate that a temp table needed to be built and reread -- such as for GROUP BY and/or ORDER BY.

I find the Handler values to be handy in comparing two queries -- bigger numbers is a good indication of a less-well optimized query.

If you like, present us with a small number of SELECTs, together with SHOW CREATE TABLE and EXPLAIN SELECT and the Handler% values. We can go into more details 'by example'.

Handler_read_key

I think read_key is bumped by one in these cases:

  • Reaching into a table, either for a specific row, or to start a range scan.
  • At least once per table in a query with multiple tables due to JOIN or subqueries.
  • Once per row in a "nested loop join". This is the usual case for a simple JOIN two tables. It scans the first table (one Handler_read_key, many Handler_read_next) and reaches into the next table repeatedly (many Handler_read_key)

Think of it this way: Most reads from a table work in 1 or 2 steps:

  • Use a "key" to find the row (or the first row of a range) --> Handler_read_key bumped by 1.
  • If multiple rows needed, then get the "next" row --> Handler_read_next bumped by 1 per extra row. If using something like ORDER BY .. DESC, then bump Handler_next_prev.
2
  • Can we say, whenever there is increase in handler_read_next value, there would be increase in handler_read_key? Under which condition, increase in handler_read_key would be more than 1 for a single query? google doc for sample queries: docs.google.com/document/d/… Apr 23, 2019 at 17:01
  • @vaibhavgupta - see if what I added clarifies it.
    – Rick James
    Apr 23, 2019 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.