A dead simple trick to detect top unindexed or badly indexed mysql queries

The ingredients are:

1. tcpdump – check your local package distributor ( apt-get, pkg_add, etc.)

2. a great maatkit tool, mk-query-digest – use wget http://www.maatkit.org/get/mk-query-digest to fetch the simple perl script, then chmod +x it, and move to a dir in path

When the stage is set, cast the following mystic spell:

tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt | mk-query-digest
--type tcpdump  --filter '($event->{No_index_used}
|| $event->{No_good_index_used})'

Of course replace eth0 with the proper interface and do similarly with the port if mysql listens on a non-default one.

Let it run for a while, possibly redirecting its stdout to a log file, then ctrl-c the beast and study the output. You will be surprised how useful this can prove.

Hello there! If you are new here, you might want to subscribe to the RSS feed for updates on this topic.The ingredients are: 1. tcpdump – check your local package...

Tagged as: database, detect, index, maatkit, mysql, performance, query, slow, tuning