MariaDB can now print query’s EXPLAIN into the slow query log! The feature is based on SHOW EXPLAIN technology and its output looks like this:
# Time: 131017 21:26:02 # User@Host: root[root] @ localhost  # Thread_id: 2 Schema: dbt3sf1 QC_hit: No # Query_time: 5.535819 Lock_time: 0.000350 Rows_sent: 1 Rows_examined: 65633 ## <explain> ## id select_type table type possible_keys key key_len ref rows Extra ## 1 SIMPLE nation ref PRIMARY,n_name n_name 26 const 1 Using where; Using index ## 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3sf1.nation.n_nationkey 25124 Using index ## 1 SIMPLE orders ref i_o_custkey i_o_custkey 5 dbt3sf1.customer.c_custkey 7 Using index ## </explain> SET timestamp=1382030762; select count(*) from customer, orders, nation where c_custkey=o_custkey and c_nationkey=n_nationkey and n_name='GERMANY';
By default, EXPLAIN is not printed to the slow query log, one needs to enable it manually like this (either from SQL or from my.cnf):
Now, I’d like to find time to hack pt-query-digest to make use of EXPLAINs. For start, let it show the number of different query plans. Then, show query plans on the response time distribution… so that one can tell which ones were fast or slow… The stopping factor here is that pt-query-digest is 500KB of unfamiliar Perl code.
Would it also be possible to get the explain output in JSON format? Then writing some script to analyze/combine the information would be easier.
Do you mean info like the above in JSON format, or output like MySQL 5.6’s “EXPLAIN FORMAT=JSON” feature?
Printing the above info in JSON form is easy. As for printing output like EXPLAIN FORMAT=JSON, it will cause the slow query log to be much bigger (because it has more info). Some of the pieces of EXPLAIN FORMAT=JSON are also computationally expensive to produce.