The ANALYZE statement
was introduced in MariaDB 10.1.0.
The ANALYZE statement
is similar to the EXPLAIN statement
. ANALYZE statement
will invoke the optimizer, execute the statement, and then produce EXPLAIN
output instead of the result set. The EXPLAIN
output will be annotated with statistics from statement execution.
This lets one check how close the optimizer's estimates about the query plan are to the reality. ANALYZE
produces an overview, while the ANALYZE FORMAT=JSON
command provides a more detailed view of the query plan and the query execution.
The syntax is
ANALYZE explainable_statement;
where the statement is any statement for which one can run EXPLAIN
.
Consider an example:
ANALYZE SELECT * FROM tbl1 WHERE key1 BETWEEN 10 AND 200 AND col1 LIKE 'foo%'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl1 type: range possible_keys: key1 key: key1 key_len: 5 ref: NULL rows: 181 r_rows: 181 filtered: 100.00 r_filtered: 10.50 Extra: Using index condition; Using where
Compared to EXPLAIN
, ANALYZE
produces two extra columns:
r_rows
is an observation-based counterpart of the rows column. It shows how many rows were actually read from the table. r_filtered
is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition. Let's consider a more complicated example.
ANALYZE SELECT * FROM orders, customer WHERE customer.c_custkey=orders.o_custkey AND customer.c_acctbal < 0 AND orders.o_totalprice > 200*1000
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 9.13 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | 10 | 100.00 | 30.03 | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
Here, one can see that
Let's modify the previous example slightly
ANALYZE SELECT * FROM orders, customer WHERE customer.c_custkey=orders.o_custkey AND customer.c_acctbal < -0 AND customer.c_comment LIKE '%foo%' AND orders.o_totalprice > 200*1000;
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+ | 1 | SIMPLE | customer | ALL | PRIMARY,... | NULL | NULL | NULL | 149095 | 150000 | 18.08 | 0.00 | Using where | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | customer.c_custkey | 7 | NULL | 100.00 | NULL | Using where | +----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
Here, one can see that orders.r_rows=NULL and orders.r_filtered=NULL. This means that table orders was not scanned even once. Indeed, we can also see customer.r_filtered=0.00. This shows that a part of WHERE attached to table `customer` was never satisfied (or, satisfied in less than 0.01% of cases).
ANALYZE FORMAT=JSON produces JSON output. It produces much more information than tabular ANALYZE
.
ANALYZE UPDATE
or ANALYZE DELETE
will actually make updates/deletes (ANALYZE SELECT
will perform the select operation and then discard the resultset). EXPLAIN ANALYZE
. ANALYZE
output of slow queries printed into the slow query log (see MDEV-6388).
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/analyze-statement/