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.
