Syntax\n------\n\nANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]\n  [PERSISTENT FOR \n    { ALL\n      | COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])\n    }\n  ]\n\nDescription\n-----------\n\nANALYZE 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.\n\nANALYZE 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.\n\nMariaDB 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.\n\nThis statement requires SELECT and INSERT privileges for the table.\n\nBy 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.\n\nANALYZE TABLE statements are not logged to the binary log if read_only is set. See also Read-Only Slaves.\n\nANALYZE TABLE is non-blocking and non-intrusive. A connection will start using new statistics for the query following the completion of the ANALYZE TABLE.\n\nANALYZE TABLE is blocking and intrusive.\n\nANALYZE TABLE is also supported for partitioned tables. You can use ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions.\n\nThe Aria storage engine supports progress reporting for the ANALYZE TABLE statement.\n\nSkipping Long CHAR/VARCHAR Columns\n\nWhen 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.\n\nThis prevents excessive disk usage when analyzing tables with large text columns.\n\n If a column is longer than analyze_max_length, it is excluded from stats.\n If a long column is explicitly specified in FOR COLUMNS(), it is still analyzed, regardless of its size.\n\nExample:\n\n``sql\nSET GLOBAL analyze_max_length = 50000;\nANALYZE TABLE large_text_table PERSISTENT;\n`\n\n`sql\nANALYZE TABLE large_text_table PERSISTENT FOR COLUMNS(long_description);\n`\n\n`sql\nCREATE TABLE product_data (\n    id INT PRIMARY KEY,\n    name VARCHAR(100),\n    description VARCHAR(50000), -- long column\n    specs VARCHAR(1000)\n);\n\n-- Set limit\nSET SESSION analyze_max_length = 10000;\n\n-- Run analysis without explicitly selecting columns\nANALYZE TABLE product_data PERSISTENT;\n\n-- 'description' will be skipped due to length > 10000\n\n-- To include it anyway\nANALYZE TABLE product_data PERSISTENT FOR COLUMNS(description);\n`\n\nThe functionality for skipping long CHAR and VARCHAR columns isn't available.\n\nPerformance Impact\n\nNote 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.\n\nRunning ANALYZE is indicated:\n\n for newly populated tables,\n for tables that have additional columns added that are used in WHERE clauses,\n when a table has doubled in size,\n 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,\n 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.\n\nANALYZE 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.\n\nEITS Statistics / PERSISTENT FOR\n\nOverview\n\nANALYZE TABLE supports engine-independent table statistics (EITS). See Engine-Independent Table Statistics: Collecting Statistics with the ANALYZE TABLE Statement for more information.\n\nYou 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:\n\n`sql\nANALYZE TABLE tbl PERSISTENT FOR ALL\n`\n\nFocusing on particular columns, the statement looks like this — it collects statistics only for the specified table columns:\n\n`sql\nANALYZE TABLE tbl PERSISTENT FOR COLUMS (column1, column2, ...) INDEXES (index1, ...)\n`\n\nFocusing on columns helps, among other things, avoid including BLOB columns, for which MariaDB doesn't collect statistics.\n\nIt'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.\n\nQueries That Benefit\n\nQueries 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:\n\n`sql\nSELECT \nFROM orders, customer\nWHERE\n       orders.o_custkey = customer.c_custkey  -- join condition\n  AND  customer.c_area = 'EMEA'  -- (1)\n  AND  orders.o_priority= 'URGENT' -- (2)\n`\n\nHere, the optimizer benefits from knowing these statistics:\n\n1. Which fraction of customers are in the EMEA region?\n2. Which fraction of orders are URGENT?\n\nFor that situation, you can issue this statement:\n\n`sql\nANALYZE TABLE orders, customer PERSISTENT FOR ALL\n`\n\nEITS vs. InnoDB-Internal Statistics\n\nEITS (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.\n\nInnoDB statistics, on the other hand, provide less data, but they are automatically updated.\n\nControlling Statistics\n\nEngine-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.\n\nThe 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.\n\nUseful Variables\n\nThe 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.\n\n| Variable                                                                                                                                                  | Introduced in MariaDB version                                  | Description                                                                                                                                                                                                                                                                                                                                            |\n| --------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |\n| 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(...)`. |\n| 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.                                                                                                                                                                                             |\n| 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.                                                                                                                                                                                                                                       |\n| 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.]]                                                                                                                                                                                                                  |\n| 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.                                                                                    |\n| log_slow_filter                      | (all versions)                                                 | Having an admin in the log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.                                                                                                                                                                                                                                                      |\n| 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.                                    |\n\nExamples\n--------\n\n-- update all engine-independent statistics for all columns and indexes\nANALYZE TABLE tbl PERSISTENT FOR ALL;\n\n-- update specific columns and indexes:\nANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);\n\n-- empty lists are allowed:\nANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();\nANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);\n\n-- the following will only update mysql.table_stats fields:\nANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();\n\n-- when use_stat_tables is set to 'COMPLEMENTARY' or 'PREFERABLY', \n-- a simple ANALYZE TABLE  collects engine-independent statistics for all columns and indexes.\nSET SESSION use_stat_tables='COMPLEMENTARY';\nANALYZE TABLE tbl;\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/table-statements/analyze-table