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.
A quick and dirty jruby mysql jdbc wrapper
I’ve started using jruby more and more, but I’m a bit unsatisfied by the current status of mysql support, the jdbc api is a bit cumbersome to me (of course only after getting lazy because of the convenience of the original ruby mysql gem) – especially about SELECTs and using their result set. Thus, I wrote a small callback wrapper, see below:
