Analyze and store key distribution. This statement updates index statistics used by the optimizer to choose the best execution plan.
ANALYZE TABLE analyzes and stores the key distribution for a table (). This statement works with , , and tables. During the analysis, InnoDB will allow reads/writes, and MyISAM/Aria reads/inserts. For MyISAM tables, this statement is equivalent to using .
ANALYZE uses histograms, which can provide a better selectivity than InnoDB statistics offer. InnoDB statistics work with a limited sample set and are therefore not as accurate as persistent statistics can be. For more information on how the analysis works within InnoDB, see .
MariaDB uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.
This statement requires for the table.
By default, ANALYZE TABLE statements are written to the and will be . The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.
ANALYZE TABLE statements are not logged to the binary log if is set. See also .
ANALYZE TABLE is non-blocking and non-intrusive. A connection will start using new statistics for the query following the completion of the ANALYZE TABLE.
ANALYZE TABLE is blocking and intrusive.
ANALYZE TABLE is also supported for partitioned tables. You can use ... ANALYZE PARTITION to analyze one or more partitions.
The storage engine supports for the ANALYZE TABLE statement.
When using ANALYZE TABLE PERSISTENT, MariaDB skips long / columns during statistics collection if they exceed the value of the system variable.
This prevents excessive disk usage when analyzing tables with large text columns.
If a column is longer than analyze_max_length
Note that analyzing tables with ANALYZE can have a performance impact and can use a lot of disk space for big tables. As column statistics usually do not change much over time, even when the table grows, there is no benefit to running ANALYZE very often.
Running ANALYZE is indicated:
for newly populated tables,
for tables that have additional columns added that are used in WHERE clauses,
when a table has doubled in size,
when you note that a query becomes slow because the table order has changed, and you can see from or that the selectivity is wrong for a table,
ANALYZE isn’t useful for table columns of type UNIQUE, PRIMARY KEY, TIME, or CURRENT_TIME. In ANALYZE queries, you should omit columns of those types.
ANALYZE TABLE supports (EITS). See for more information.
You can run the statement on all columns with this statement — however, be aware that this can take a long time for very large (500+ GB) tables:
Focusing on particular columns, the statement looks like this — it collects statistics only for the specified table columns:
Focusing on columns helps, among other things, avoid including BLOB columns, for which MariaDB doesn't collect statistics.
Queries that benefit most are those where the query plan depends on the knowing condition selectivities, the most important ones being JOIN queries and those with an ORDER BY ... LIMIT clause. To benefit, the query must have a condition that may or may not be selective, for example:
Here, the optimizer benefits from knowing these statistics:
Which fraction of customers are in the EMEA region?
Which fraction of orders are URGENT?
For that situation, you can issue this statement:
EITS (engine-independent table statistics) provide way more data than InnoDB-internal statistics. The downside is that EITS are never automatically updated, and it takes time to collect them.
InnoDB statistics, on the other hand, provide less data, but they are automatically updated.
Engine-independent statistics can be controlled (enabled and disabled) using the and the . InnoDB-persistent statistics are controlled with the (allowing you to turn InnoDB statistics on or off). Combining both kinds of statistics is possible.
The server relies on InnoDB statistics by default. That way, it can use some statistics even if ANALYZE TABLE is never run (or not often enough). This gives good enough results for the majority of queries. Some queries, however, need more statistical data so the optimizer can create a good plan. Slow queries indicate there aren't enough statistical data. Those queries can be accelerated by running ANALYZE TABLE tbl PERSISTENT FOR ..., where tbl indicates a table used by a slow query. You can also run ANALYZE TABLE ... PERSISTENT FOR ALL, but that has a significant performance impact.
The following overview indicates when a particular variable was introduced. When multiple versions are given, it means variable options (like the default value) changed between the indicated versions.
(mariadb.org blog)
This page is licensed: GPLv2, originally from
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
[PERSISTENT FOR
{ ALL
| COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
}
]If a long column is explicitly specified in FOR COLUMNS(), it is still analyzed, regardless of its size.
Example:
The functionality for skipping long CHAR and VARCHAR columns isn't available.
when the distribution of data in a table has changed significantly, for example, if you have loaded a batch of data or deleted a large amount of data.
11.0.1
Log slow OPTIMIZE, ANALYZE, ALTER, and other administrative statements to the slow log if it is open. Deprecated. Use instead.
(all versions)
Having an admin in the log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.
(all versions)
For calculating the number of duplicates, ANALYZE TABLE uses a buffer of bytes per column. You can slightly increase the speed of ANALYZE TABLE by increasing this variable.
MariaDB 10.6.23 / 10.11.14 / 11.4.8 / 11.8.3 / 12.0.2 / 12.1.1
10.4.3
Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics. Set to 0 to let MariaDB decide what percentage of rows to sample.
10.4.3-11.0
Specifies the type of histograms created by ANALYZE. Options are #SINGLE_PREC_HB,DOUBLE_PREC_HB or JSON_HB.
10.7
Number of bytes or buckets (in case of JSON_HB) used for storing the histogram. If set to 0, no histograms are created by ANALYZE.]]
ANALYZE TABLE tbl PERSISTENT FOR ALLANALYZE TABLE tbl PERSISTENT FOR COLUMS (column1, column2, ...) INDEXES (index1, ...)SELECT *
FROM orders, customer
WHERE
orders.o_custkey = customer.c_custkey -- join condition
AND customer.c_area = 'EMEA' -- (1)
AND orders.o_priority= 'URGENT' -- (2)ANALYZE TABLE orders, customer PERSISTENT FOR ALL-- update all engine-independent statistics for all columns and indexes
ANALYZE TABLE tbl PERSISTENT FOR ALL;
-- update specific columns and indexes:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);
-- empty lists are allowed:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);
-- the following will only update mysql.table_stats fields:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();
-- when use_stat_tables is set to 'COMPLEMENTARY' or 'PREFERABLY',
-- a simple ANALYZE TABLE collects engine-independent statistics for all columns and indexes.
SET SESSION use_stat_tables='COMPLEMENTARY';
ANALYZE TABLE tbl;SET GLOBAL analyze_max_length = 50000;
ANALYZE TABLE large_text_table PERSISTENT;ANALYZE TABLE large_text_table PERSISTENT FOR COLUMNS(long_description);CREATE TABLE product_data (
id INT PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(50000), -- long column
specs VARCHAR(1000)
);
-- Set limit
SET SESSION analyze_max_length = 10000;
-- Run analysis without explicitly selecting columns
ANALYZE TABLE product_data PERSISTENT;
-- 'description' will be skipped due to length > 10000
-- To include it anyway
ANALYZE TABLE product_data PERSISTENT FOR COLUMNS(description);