Description
-----------

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.

Command Output
--------------

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.

Interpreting the Output
-----------------------

Joins
-----

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

* For table customer, customer.rows=149095,  customer.r_rows=150000. The
estimate for number of rows we will read was fairly precise
* customer.filtered=18.08, customer.r_filtered=9.13.  The optimizer somewhat
overestimated the number of records that will match selectivity of condition
attached to `customer` table (in general, when you have a full scan and
r_filtered is less than 15%, it's time to consider adding an appropriate
index).
* For table orders,  orders.rows=7, orders.r_rows=10.  This means that on
average, there are 7 orders for a given c_custkey, but in our case there were
10, which is close to the expectation (when this number is consistently far
from the expectation, it may be time to run ANALYZE TABLE, or even edit the
table statistics manually to get better query plans).
* orders.filtered=100, orders.r_filtered=30.03. The optimizer didn't have any
way to estimate which fraction of records will be left after it checks the
condition that is attached to table orders (it's orders.o_totalprice >
200*1000). So, it used 100%. In reality, it is 30%. 30% is typically not
selective enough to warrant adding new indexes. For joins with many tables, it
might be worth to collect and use column statistics for columns in question,
this may help the optimizer to pick a better query plan.

Meaning of NULL in r_rows and r_filtered
----------------------------------------

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
-------------------

ANALYZE FORMAT=JSON produces JSON output. It produces much more information
than tabular ANALYZE.

Notes
-----

* ANALYZE UPDATE or ANALYZE DELETE will actually make updates/deletes (ANALYZE
SELECT will perform the select operation and then discard the resultset).
* PostgreSQL has a similar command, EXPLAIN ANALYZE.
* The EXPLAIN in the slow query log feature allows MariaDB to have ANALYZE
output of slow queries printed into the slow query log (see MDEV-6388).

URL: https://mariadb.com/kb/en/analyze-statement/