MySQL status variable Handler_read_rnd_next is growing a lot

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.

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.

StackExchange Database Administrators Q#10865, answer score: 7