ANALYZE statement
You are viewing an old version of this article. View
the current version here.
MariaDB starting with 10.0.0
The ANALYZE statement
command is a new feature in MariaDB 10.1.0.
Contents
Command description
The ANALYZE statement
command is similar to EXPLAIN statement
. ANALYZE
statement
will invoke the optimizer, execute the statement, and then produce EXPLAIN
output, annotated with statistics from statement execution.
This lets one check how optimizer's estimates about the query plan are close to the reality.
The syntax is
ANALYZE explainable_statement;
where the statement is any statement for which one can run EXPLAIN.
Output basics
Consider an example:
MariaDB> 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 rows column. It shows how many rows were actually read from the table.
- r_filtered is an observation-based counterpart of filtered column. It shows which fraction of rows was left after applying the WHERE condition.
Output details
Joins
Let's consider a more complicated example.
TODO joins
Meaning of r_rows=NULL
.
Notes
ANALYZE UPDATE
orANALYZE DELETE
will actually make updates/deletes.- PostgreSQL has a similar command,
EXPLAIN ANALYZE
.
See also
- the Jira task is: https://mariadb.atlassian.net/browse/MDEV-406
Comments
Comments loading...
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.