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.

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.

Thanks!

share|improve this question
    
My guess is that a derived table is a table without indexes, so that counter is updated. –  ypercube Jun 29 '12 at 12:06
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks! I will keep this in mind –  tnosaj Jul 2 '12 at 14:21
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.