What's new in mk-query-digest: EXPLAIN sparklines
Posted in Databases on Dec 17, 2010
This month’s Maatkit release has some nice new features and improvements to mk-query-digest. There is one that deserves its own blog post: EXPLAIN sparklines.
A “sparkline” is a simple type of chart that conveys important information without the details. We added a kind of ASCII sparkline to mk-query-digest to convey important information about the query’s EXPLAIN plan so you can see if the query is “bad” or not. It is kind of a cryptic geek code that you will need some help decoding. It’s intentionally compact, so that it can fit in the “profile” that mk-query-digest prints out from a normal report.
Here is an example of the profile report:
# Profile # Rank Query ID Response time Calls R/Call Apdx V/M EXPLAIN Item # ==== ================== ================ ===== ======== ==== ===== ======= ===== # 1 0x808CDA06B6EB3D5A 0.0141 83.5% 2 0.0071 1.00 0.00 aa SELECT test.t # 2 0x8305A7D4195D2096 0.0011 6.7% 6 0.0002 1.00 0.00 aa SELECT test.t
The EXPLAIN column appears if you add the –explain option to mk-query-digest. Note: while writing this post I discovered a bug in the new functionality, which is now fixed in trunk, so if you want to use this you’ll need to ‘wget maatkit.org/trunk/mk-query-digest’ to get a version that doesn’t have the bug.
In this case, both queries are shown as aa. What is that? It’s our geek code, one character per table in the EXPLAIN plan. And if we look at the documentation, ‘a’ is the shorthand for Type=ALL:
The abbreviated table access codes are: a ALL c const e eq_ref f fulltext i index m index_merge n range o ref_or_null r ref s system u unique_subquery
So “aa” is shorthand for “table scan the first table, and do a cross-join with the second table by scanning it too.” That’s a terrible query plan! Someone needs to fix their SQL or add some indexes or something.
The code includes a couple of other small but important bits of data about the EXPLAIN plan:
Here are some more examples so you can practice reading the results: