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.
Related posts:
8 comments
Trackbacks/Pingbacks
- Twitter Trackbacks for A dead simple trick to detect top unindexed or badly indexed mysql queries | blog@iamnolegend.com [ochronus] [iamnolegend.com] on Topsy.com - [...] A dead simple trick to detect top unindexed or badly indexed mysql queries | blog@iamnolegend.com [o... blog.iamnolegend.com/a-dead-simple-trick-to-detect-top-unindexed-or-badly-indexed-mysql-queries – ...
Leave a Reply
Additional comments powered by BackType
A simple trick to detect unindexed or badly indexed mysql queries: http://bit.ly/zu1r9
This comment was originally posted on Twitter
[Reply]
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]
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]
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]
A dead simple http://bit.ly/QPEsI
#mysql (2 tweets)
This comment was originally posted on Twitter
[Reply]
A simple way to detect un-indexed SQL queries in #mysql – http://bit.ly/zu1r9
This comment was originally posted on Twitter
[Reply]
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]