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

Posted by ochronus on Aug 19, 2009 in database, scalability, tuning |

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 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.

A quick and dirty jruby mysql jdbc wrapper

6 Tweets

8 Responses to “A dead simple trick to detect top unindexed or badly indexed mysql queries”

ochronus says:
August 19, 2009 at 20:55

A simple trick to detect unindexed or badly indexed mysql queries: http://bit.ly/zu1r9

This comment was originally posted on Twitter

[Reply]
tyjak says:
August 20, 2009 at 09:00

A simple trick to detect unindexed or badly indexed mysql queries: http://bit.ly/zu1r9 ( RT @ochronus)

This comment was originally posted on Twitter

[Reply]
briancray says:
August 30, 2009 at 00:06

A dead simple trick to detect top unindexed or badly indexed mysql queries http://tr.im/xrpO

This comment was originally posted on Twitter

[Reply]
hmarquardt says:
August 30, 2009 at 01:15

Good mysql tip RT @briancray A dead simple trick to detect top unindexed or badly indexed mysql queries http://tr.im/xrpO

This comment was originally posted on Twitter

[Reply]
mysql_topsy says:
August 30, 2009 at 01:16

A dead simple http://bit.ly/QPEsI
#mysql (2 tweets)

This comment was originally posted on Twitter

[Reply]
TimIgoe says:
August 30, 2009 at 01:20

A simple way to detect un-indexed SQL queries in #mysql – http://bit.ly/zu1r9

This comment was originally posted on Twitter

[Reply]
Testy McTester says:
February 19, 2010 at 01:27

MySQL already has this functionality built in:

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_log-queries-not-using-indexes

[Reply]
ochronus Reply:
February 19th, 2010 at 06:07
@Testy McTester, thanks, nice find, but it’s deprecated and it doesn’t do the same thing, check the description again :)

[Reply]