ANALYZE TABLE

Analyze and store key distribution. This statement updates index statistics used by the optimizer to choose the best execution plan.

Syntax

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
  [PERSISTENT FOR 
    { ALL
      | COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
    }
  ]

Description

ANALYZE TABLE analyzes and stores the key distribution for a table (index statistics). This statement works with MyISAM, Aria, and InnoDB tables. During the analysis, InnoDB will allow reads/writes, and MyISAM/Aria reads/inserts. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

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 InnoDB Limitations.

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 SELECT and INSERT privileges for the table.

By default, ANALYZE TABLE statements are written to the binary log and will be replicated. 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 read_only is set. See also Read-Only Replicas.

Current

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.

< 10.6.16

ANALYZE TABLE is also supported for partitioned tables. You can use ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions.

The Aria storage engine supports progress reporting for the ANALYZE TABLE statement.

Current

Skipping Long CHAR/VARCHAR Columns

From MariaDB 10.6.23, MariaDB 10.11.14, MariaDB 11.4.8, MariaDB 11.8.3, MariaDB 12.0.2, MariaDB 12.1.1, and MariaDB Enterprise 11.8, when using ANALYZE TABLE PERSISTENT, MariaDB skips long CHAR/VARCHAR columns during statistics collection if they exceed the value of the analyze_max_length system variable.

This prevents excessive disk usage when analyzing tables with large text columns.

• If a column is longer than analyze_max_length, it is excluded from stats.

• If a long column is explicitly specified in FOR COLUMNS(), it is still analyzed, regardless of its size.

Example:

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

< 10.6.23 / 10.11.14 / 11.4.8 / 11.8.3 / 12.0.2 / 12.1.1 / 11.8 Enterprise Server

Performance Impact

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 EXPLAIN or ANALYZE FORMAT=JSON that the selectivity is wrong for a table,

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

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.

EITS Statistics / PERSISTENT FOR

Overview

ANALYZE TABLE supports engine-independent table statistics (EITS). See Engine-Independent Table Statistics: Collecting Statistics with the ANALYZE TABLE Statement 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:

ANALYZE TABLE tbl PERSISTENT FOR ALL

Focusing on particular columns, the statement looks like this — it collects statistics only for the specified table columns:

ANALYZE TABLE tbl PERSISTENT FOR COLUMS (column1, column2, ...) INDEXES (index1, ...)

Focusing on columns helps, among other things, avoid including BLOB columns, for which MariaDB doesn't collect statistics.

It's recommended to collect statistics for columns that do not have an index starting from that column (if the index starts with a column, the index itself is used as a source for statistics), and which are used in WHERE conditions of your queries.

Queries That Benefit

Queries that benefit most are those where the query plan depends on the optimizer 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:

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)

Here, the optimizer benefits from knowing these statistics:

1. Which fraction of customers are in the EMEA region?

2. Which fraction of orders are URGENT?

For that situation, you can issue this statement:

ANALYZE TABLE orders, customer PERSISTENT FOR ALL

EITS vs. InnoDB-Internal Statistics

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.

Controlling Statistics

Engine-independent statistics can be controlled (enabled and disabled) using the use_stat_tables variable and the optimizer_use_condition_selectivity variable. InnoDB-persistent statistics are controlled with the innodb_stats_persistent variable (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.

Useful Variables

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.

Variable	Introduced in MariaDB version	Description
analyze_max_length	MariaDB 10.6.23 / 10.11.14 / 11.4.8 / 11.8.3 / 12.0.2 / 12.1.1	Prevents the collection of column statistics for CHAR/VARCHAR columns that can be analyzed automatically by ANALYZE TABLE PERSISTENT. Columns exceeding this threshold in bytes will be skipped unless included explicitly in FOR COLUMNS(...).
analyze_sample_percentage	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.
histogram_type	10.4.3-11.0	Specifies the type of histograms created by ANALYZE. Options are #SINGLE_PREC_HB,DOUBLE_PREC_HB or JSON_HB.
histogram_size	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.]]
log_slow_admin_statements	11.0.1	Log slow OPTIMIZE, ANALYZE, ALTER, and other administrative statements to the slow log if it is open. Deprecated. Use log_slow_filter instead.
log_slow_filter	(all versions)	Having an admin in the log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.
sort_buffer_size	(all versions)	For calculating the number of duplicates, ANALYZE TABLE uses a buffer of sort_buffer_size bytes per column. You can slightly increase the speed of ANALYZE TABLE by increasing this variable.

Examples

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

See Also

• This one trick can make MariaDB 30x faster! (mariadb.org blog)

• Index Statistics

• InnoDB Persistent Statistics

• Progress Reporting

• Engine-independent Statistics

• Histogram-based Statistics

• ANALYZE Statement

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}