The handler_read_* STATUS VARIABLES

Http://www.fromdual.com/mysql-handler-read-status-variables

Because I do a lot of performance Tuning gigs I get often in the contact with these status variables. In the beginning I had a problem to understand them and now I had a problem to memorize the relation of the name and the Meaning. Therefore I wrote this little summary:

PREPARE the EXAMPLE

To show you the effect I has worked out a little example:

CREATE TABLE Test (    ID    INT UNSIGNED not NULL auto_increment PRIMARY KEY  , Data  VARCHAR (+)  , TS    T  Imestamp, INDEX (data)), INSERT into testvalues (null, ' ABC ', now ()), (NULL, ' ABC ', now ()),  (null, ' Abd ', Now ())     ,  (NULL, ' ACD ', Now ()), (null, ' Def ', Now ()), (null, ' PQR ', Now ()), (null, ' Stu ', Now ()), (null,     ' VWX ', Now ()), (NULL, ' Yza ', Now ())     , (NULL, ' Def ', now ()); SELECT * FROM test;+----+------+---------------------+| ID | Data | TS                  |+----+------+---------------------+|  1 | ABC  | 2008-01-18 16:28:40 | |  2 | ABC  | 2008-01-18 16:28:40 | |  3 | Abd  | 2008-01-18 16:28:40 | |  4 | ACD  | 2008-01-18 16:28:40 | |  5 | def  | 2008-01-18 16:28:40 | |  6 | PQR  | 2008-01-18 16:28:40 | |  7 | Stu  | 2008-01-18 16:28:40 | |  8 | VWX  | 2008-01-18 16:28:40 | |  7 2 Yza  | 2008-01-18 16:28:40 | | | def  | 2008-01-18 16:28:40 |+----+------+---------------------+

To see the effect of a query do the following steps:

1. FLUSH STATUS;
2. Execute the query
3. SHOW SESSION STATUS LIKE ‘handler_read%‘;
4. Do an of the EXPLAIN query

Handler_read_first

The number of times the first entry is read from the index. If This value was high, it suggests the the server is doing a lot of full index scans.

+-------------+          +---+---+|          Table | |             In|ex | |          |   |   |             ||          |   |   |             ||          |   |   |             ||          |   |   |             ||          |   |             V | |          |       |             ||          |       | |+-------------+ +-------+select data from test;10 rows in set+-----------------------+-------+| variable_name | Value |+-----------------------+-------+| Handler_read_first | 1 | | Handler_read_key | 0 | | Handler_read_next | |+-----------------------+-------+explain SELECT data from test;+----+-------------+-------+-------+------------ ---+------+---------+------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | Simple | Test | Index | NULL | Data | 35 | NULL | 10 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

So what we can basically say are, that we had 1 full index scan and it did 10+1 index fetches.

Let us does some more examples

SELECT data from test WHERE data between ' A ' and ' O ', 6 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 1     | | Handler_read_next     | 6     |+-----------------------+-------+ +----+-------------+-------+-------+----------- ----+------+---------+------+------+--------------------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows | Extra                    |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+|  1 | Simple      | Test  | range | data |          Data      | NULL |    5 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+

Here it seems the query was not starting with Handler_read_first though it could theoretically. Instead of We get a handler_read_key. What we can also see are the "wrong" estimation of the optimizer in the execution plan.

Whit This example the query really could start from the beginning ...

SELECT data from Test WHERE data < ' O '; 6 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 1     | | Handler_read_next     | 6     |+-----------------------+-------+ +----+-------------+-------+-------+----------- ----+------+---------+------+------+--------------------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows | Extra                    |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+|  1 | Simple      | Test  | range | data |          Data      | NULL |    5 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------

But it does not!

The same for this query:

SELECT data from test WHERE data like ' a% ', 4 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 1     | | Handler_read_next     | 4     |+-----------------------+-------+ +----+-------------+-------+-------+----------- ----+------+---------+------+------+--------------------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows | Extra                    |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+|  1 | Simple      | Test  | range | data |          Data      | NULL |    4 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+

And this query does something completely different:

SELECT data from Test WHERE data in (' abc ', ' Abd ', ' ACD '); 4 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 3     | | Handler_read_next     | 4     |+-----------------------+-------+ +----+-------------+-------+-------+----------- ----+------+---------+------+------+--------------------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows | Extra                    |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+|  1 | Simple      | Test  | range | data |          Data      | NULL |    4 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+---------------- ----------+

I was not able to get any handler_read_first count other than by a real full index scan. So I would say, a handler_read_first is equivalent to number of the full index scans.

A full index scan was better than a full table scan but still not good because they burn a lot of CPU cycles. But sometimes cannot avoid it ...

Handler_read_key

The number of requests to read a row based on a key. If This value was high, it was a good indication that your tables was properly indexed for your queries.

See also the examples in the previous chapter.

+-------------+          +-------+|          Table | |             Index | | |       <------| |             <--+|          |       |             ||          |       |             ||          |       |             ||          |       |             ||          |       |             ||          |       | |+-------------+ +-------+select data from test where data = ' abc ', 2 rows in set+-----------------------+-------+ | variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 2 |+-----------------------+-------+ +----+-------------+-------+------+---------------+------+---------+-------+ ------+--------------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------  -------------+| 1 | Simple | Test |  Ref| Data | Data | 35 |    Const | 2 | Using where; Using index |+----+-------------+-------+------+---------------+------+---------+-------+------+---------------- ----------+

What makes me wondering in this example (an also in the previous) are, that based on the query there are IMHO no reason to a Ccess the table (row) ...

SELECT * FROM test where data = ' PQR ', 1 row in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 1     | | Handler_read_next     | 1     |+-----------------------+-------+ +----+-------------+-------+------+------------ ---+------+---------+-------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref   | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+|  1 | Simple      | Test  | ref  | data          | data | |      Const |    1 | Using where |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

In this example it makes clearly sense...!

Handler_read_next

The number of requests to read the next row in key order. This value was incremented if querying an index column with a range constraint or if you are doing an index scan.

See also the examples in the previous chapters.

+-------------+          +-------+| Table       |          | Index | | | | | | |             <------  |   +   | |             | <------  |   | | |             | <------  |   V | | | | | | | | | | | | |       +-------------+          +-------+

Handler_read_prev

The number of requests to read the previous row in key order. This read method was mainly used to optimize ORDER by ... DESC.

+-------------+          +-------+|          Table | |             Index | |          |       |             || |   <------|             ^   || |   <------|   |             || |   <------|             +   ||          |       |             ||          |       |             ||          |       | |+-------------+ +-------+select data from Test ORDER by data desc;10 rows in set+-----------------------+------- +| variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | |+-----------------------+-------+ +----+-------------+-------+-------+---------------+------+---------+------ +------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | Simple | Test | Index | Null          | Data | 35 |   NULL | 10 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

There is no such status like Handler_read_last implemented like it could being according to the Handler functions [1].

SELECT * from test where data between ' A ' and ' B ' ORDER by data desc;4 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     | | Handler_read_key      | 1     | | Handler_read_next     | 0     | | Handler_read_prev     | 4     |+-----------------------+-------+ +----+-------------+-------+-------+----------- ----+------+---------+------+------+-------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+|  1 | Simple      | Test  | range | data |          Data      | NULL |    4 | Using where |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

Handler_read_rnd

The number of requests to read a row based on a fixed position. This value is doing a lot of queries that require sorting of the result. You probably has a lot of queries so require MySQL to scan entire tables or you had joins that don ' t use keys properly .

This status comes to account if the old file_sort mechanism is used [2].

To do this, we have the modify slightly our table:

ALTER TABLE Test ADD COLUMN file_sort text; UPDATE Test SET file_sort = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ ' WHERE id = 1; UPDATE Test SET file_sort = ' Bcdefghijklmnopqrstuvwxyza ' WHERE id = 2; UPDATE Test SET file_sort = ' Cdefghijklmnopqrstuvwxyzab ' WHERE id = 3; UPDATE Test SET file_sort = ' DEFGHIJKLMNOPQRSTUVWXYZABC ' WHERE id = 4; UPDATE Test SET file_sort = ' EFGHIJKLMNOPQRSTUVWXYZABCD ' WHERE id = 5; UPDATE Test SET file_sort = ' FGHIJKLMNOPQRSTUVWXYZABCDE ' WHERE id = 6; UPDATE Test SET file_sort = ' ghijklmnopqrstuvwxyzabcdef ' WHERE id = 7; UPDATE Test SET file_sort = ' HIJKLMNOPQRSTUVWXYZABCDEFG ' WHERE id = 8; UPDATE Test SET file_sort = ' IJKLMNOPQRSTUVWXYZABCDEFGH ' WHERE id = 9; UPDATE Test SET file_sort = ' Jklmnopqrstuvwxyzabcdefghi ' WHERE id = 10;

SELECT * FROM Test ORDER by File_sort asc;10 rows in set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_rnd | |    | Handler_read_rnd_next |    |+-----------------------+-------+ +----+-------------+-------+------+---------------+------+---------+-- ----+------+----------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | Simple      | Test  | All  | NULL          | NULL | NULL    | NULL |   10 | Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+-------------- --+

This was really a performance killer and should be avoided whenever possible!

Handler_read_rnd_next

The

The number of requests to read the next row in the data file. This value was high if you are doing a lot of table scans. Generally this suggests that your tables is not properly indexed or that your queries is not written to take advantage O f The indexes you have.

 +------+------+ +-------+|      table|          | |      Index | |      |          |       |      ||      |          |       |      ||      |          |       |      ||      |          |       |      ||          V |       |             ||          |       |             ||          |       | |+-------------+ +-------+select * from test;10 rows in set+-----------------------+-------+| variable_name | Value |+-----------------------+-------+| Handler_read_rnd_next | |+-----------------------+-------+ +----+-------------+-------+------+---------------+------+---------+------+ ------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | Simple | Test | All | NULL | NULL | NULL |   NULL |       10 | |+----+-------------+-------+------+---------------+------+---------+------+------+-------+

Obviously also filtering does not has a impact on the work which is performed:

SELECT * FROM test WHERE ts = ' 2008-01-18 17:33:39 '; Empty set+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_rnd_next |    |+-----------------------+-------+ +----+-------------+-------+------+---------------+------+---------+-- ----+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+|  1 | Simple      | Test  | All  | NULL          | NULL | NULL    | NULL |   10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

The handler_read_* STATUS VARIABLES