Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

3 Answers

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.

share|improve this answer
add comment

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)
share|improve this answer
add comment

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.

share|improve this answer
    
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 '12 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. –  RolandoMySQLDBA May 2 '12 at 12:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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