Find Query Problems Proactively With Query Reviews

Wikipedia defines a code review as a “systematic examination (often as peer review) of computer source code intended to find and fix mistakes overlooked in the initial development phase, improving both the overall quality of software and the developers’ skills.”

Wouldn’t it be great to do the same thing, but for the queries run against your databases? This presentation shows you how to do exactly that!

mk-query-digest is a Maatkit tool that can digest queries (similar to the output of mysqldumpslow) in a report. This can be a one-off report, or it can be stored in MySQL for a meta-database of queries. You can use this process to systematically go through every type of query that is sent by the code and set about approving or annotating each query type. For example, I have used this system to EXPLAIN each type of query, so that queries can be optimized before they show up in the slow query log.

This presentation will show how to review the queries in your system on a one-time or ongoing basis. Being able to review new queries before code is released allows you to catch problematic queries before it goes to production. By saving the results in MySQL, you can take the large project of “make sure every query is optimized” and break it into manageable chunks.