EXPLAIN in the Slow Query Log

Starting from MariaDB 10.0.4, it is possible to have EXPLAIN output printed in the slow query log.

Switching it on

EXPLAIN can be switched on by specifying "explain" keyword in the log_slow_verbosity system variable. Alternatively, you can specify in the log-slow-verbosity command line argument.

[mysqld]
log-slow-verbosity=query_plan,explain

How it looks like

When explain printing is on, slow query log entries look 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';