I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of "Handler_read_rnd_next" calls.

After doing some digging in the mysql dev docu, this handler supposedly indicates: "this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have"

I then started analyzing our queries for non-indexed calls with the help of tcpdump and mk-query-digest (awesome tool btw) with the options:

  • --filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

but am drawing a blank: all the queries which are being listed as not using an index are ones which have a "derived" select type in them somewhere and all finish within a few milliseconds.

Hence my question: if the derived select types are the only ones without indexes, where are my Handler_read_rnd_next instances coming from? I'd appreciate any help i can get on trying to find out what the issue is.


  • My guess is that a derived table is a table without indexes, so that counter is updated. Jun 29, 2012 at 12:06

1 Answer 1


You should enable slow-query-log and log-queries-not-using-indexes to in order to manifest what queries are skipping on the indexes.

This is just a personal preference, but I usually the --processlist option of mk-query-digest. I wrote a automation script for it long ago : MySQL general query log performance effects based on Percona's video about mk-query-digest being a replacement for the slow log.

Once the queries are made known, you will have run EXPLAIN on all selects. Any query whose EXPLAIN plan fails to choose an index is guilty of doing the table scans.


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.