EXPLAIN in the Slow Query Log - Source

Revision: 115592
User: Sergei Petrunia
Date: 2022-04-26 21:30

== Switching it On

[[explain|EXPLAIN]] output can be switched on by specifying the "<<fixed>>explain<</fixed>>" keyword in the [[server-system-variables#log_slow_verbosity|log_slow_verbosity]] system variable. Alternatively, you can set with the <<fixed>>log-slow-verbosity<</fixed>> command line argument.

<<code>>
[mysqld]
log-slow-verbosity=query_plan,explain
<</code>>

EXPLAIN output will only be recorded if the slow query log is written to a file (and not to a table - see [[writing-logs-into-tables|Writing logs into tables]]). This limitation also applies to other extended statistics that are written into the slow query log.

== What it Looks Like
When explain recording is on, slow query log entries look like this:

<<code wrap=true>>
# Time: 131112 17:03:32
# User@Host: root[root] @ localhost []
# Thread_id: 2  Schema: dbt3sf1  QC_hit: No
# Query_time: 5.524103  Lock_time: 0.000337  Rows_sent: 1  Rows_examined: 65633
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    Extra
# explain: 1    SIMPLE  nation  ref     PRIMARY,n_name  n_name  26      const   1       Using where; Using index
# explain: 1    SIMPLE  customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3sf1.nation.n_nationkey      3145    Using index
# explain: 1    SIMPLE  orders  ref     i_o_custkey     i_o_custkey     5       dbt3sf1.customer.c_custkey      7       Using index
#
SET timestamp=1384261412;
select count(*) from customer, orders, nation where c_custkey=o_custkey and c_nationkey=n_nationkey and n_name='GERMANY';
<</code>>

EXPLAIN lines start with <<code>># explain:<</code>>.

== See Also ===
* MDEV-407

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

EXPLAIN in the Slow Query Log - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB