In MYSQL status, Handler_read_rnd_next value is very high.

I am aware that, this value will be incremented when a query is executed which is not having proper indexes.

But, even when we execute show status like 'Handler_read_rnd_next', this value is getting incremented by 2.

Based on this status flag, we are monitoring some stats.

So every time, this stats are showing critical.

Can we exclude these 'show' execution counts from 'Handler_read_rnd_next' count.

One more example for this,

There is a table with 10 rows, table is indexed on column 'data', and if we execute the following query:

select data from test where data = 'vwx' -> returns one row

and if we check value of 'Handler_read_rnd_next', it got incremented by 7.

Following is result of explain command for the above query:

explain select data from test where data = 'vwx';

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, 'SIMPLE', 'test', 'ref', 'data', 'data', '35', 'const', 1, 'Using where; Using index'

Is there any way to restrict this value, or can i know why this value is getting incremented very fast.

  • Is this actually causing a performance problem? Apr 2, 2012 at 13:04
  • No performance is not getting affected, but monitoring tool is checking this flag and showing critical.
    – Phanindra
    Apr 3, 2012 at 10:34
  • If performance is not a problem, then fix the monitoring tool instead. Apr 3, 2012 at 12:12
  • I had checked with other tools as well (Monyog), there also same issue.
    – Phanindra
    Apr 6, 2012 at 13:39
  • so what? Ignore it if it's not causing a performance problem. It's just a counter. Apr 7, 2012 at 10:07

3 Answers 3


First of all, let us look at the definition of Handler_read_rnd_next.

According to the MySQL Documentation on Handler_read_rnd_next:

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

Now, look at your query:

select data from test where data = 'vwx';

You said that the table has 10 rows. As a rule of thumb, the MySQL Query Optimizer will dismiss the use of an index if the number of rows that needs to be examined is greater that 5% of the total number of rows.

Let us do the math. 5% of 10 rows is 0.5 rows. Even if the number of rows need to locate your data is 1, that is greater than 0.5. Based of this lower number of rows and that index rule I just mentioned, MySQL Query Optimizer will always do a table scan.

Since the column data is itself indexed, instead of a table scan, mysql was performed an index scan.

If you know for a certainty that the test table will never grow, you can remove all indexes and let table scans happen. Handler status variables should stop incrementing.

  • Hi, thanks for the reply. I had tried by removing the index and checked the value by executing the query. But the value of Handler_read_rnd_next is incrementing by 18 which was incrementing by 7 with index. The table what I mentioned is not fixed. That is an example, I inserted 70 more rows into the table, so total rows is 80 and executed the same query with index on column 'data' which still returns only one row. But still when I check the value of 'Handler_read_rnd_next', it is still incrementing by 7. Can I know the reason how this flag gets incremented and how to restrict this.
    – Phanindra
    Jan 19, 2012 at 7:46
  • The exact same reasons I gave still apply. An index scan was performed. This time, a full index was not needed. Evidently, 7 leaf nodes in the BTREE of the index had to be traversed to get the one row. Handler status counters reveal index usage. The only way to restrict is to remove the index altogether as I stated. Otherwise, this is always expected behavior. Better indexeing of more complex table structures and properly designed queries can mitigate handler counting but can never remove them completely. May 2, 2012 at 12:32
  • "As a rule of thumb, the MySQL Query Optimizer will dismiss the use of an index if the number of rows that needs to be examined is greater that 5% of the total number of rows." - this is very very helpful to know. Is there any official documentation that supports this? Thank you so much!
    – itoctopus
    Sep 24, 2019 at 8:59

What version of MySQL?

The reasons why this flag gets incremented is best documented here: http://www.mysqlperformanceblog.com/2010/06/15/what-does-handler_read_rnd-mean/

In short, it's just the counter of the number of rows fetched in order during a full or partial table scan.

Now, that said, I'm getting a different result:

mysql> CREATE TABLE `test` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `data` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `data` (`data`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO test (data) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i'), ('vwx');
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.07 sec)

mysql> select data from test where data = 'vwx';
| data |
| vwx  |
1 row in set (0.04 sec)

| Variable_name              | Value |
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 3     |
| Handler_read_last          | 0     |
| Handler_read_next          | 1     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
16 rows in set (0.15 sec)

If there is an Unique/primary index on "data" column then you have already done optimization for this query. I can't think further optimization can be done on this.

Also you can verify whether there is FULL TABLE SCAN has been done or not?

SHOW STATUS like 'select_scan'; 
SELECT data from test where data='vmx';
SHOW STATUS like 'select_scan'; 

Make sure that select_scan hasn't increased it's value, in this way you can check whether FULL TABLE SCAN is done or not, You should try to optimize a query that will not do FULL TABLE SCAN.

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.