MySQL status variable Handler_read_rnd_next is growing a lot

Question

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.

Answer 1

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.

Answer 2

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

mysql> FLUSH STATUS;
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)

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| 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)

Answer 3

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.