Analyze statistics for the table, index, columns in MariaDB

Gather Statistics for table, index, columns in MariaDB

Check the Statistics for the Table, Index & Columns in MariaDB.

--Check table stats:
Select * from mysql.table_stats;

--Check for index stats:
Select * from mysql.index_stats;

--Check columns stats:
select * from mysql.column_stats where table_name = 'customers'

Analyze and update the statistics for the table, Index and columns:


Gather the stats for table and columns (histogram) 

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

-- update all statistics for all columns and indexes
ANALYZE TABLE table_name PERSISTENT FOR ALL;

-- update specific columns and indexes:
ANALYZE TABLE table_name PERSISTENT FOR COLUMNS (co11,col2.....) INDEXES (idx1,idx2,...);

-- empty lists are allowed:
ANALYZE TABLE table_name PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE table_name PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);

-- the following will only update mysql.table_stats fields:
ANALYZE TABLE table_name PERSISTENT FOR COLUMNS () INDEXES ();

-- Example 
MariaDB [classicmodels]> ANALYZE TABLE offices PERSISTENT FOR ALL;
+-------------------------+---------+----------+-----------------------------------------+
| Table                   | Op      | Msg_type | Msg_text                                |
+-------------------------+---------+----------+-----------------------------------------+
| classicmodels.customers | analyze | status   | Engine-independent statistics collected |
| classicmodels.customers | analyze | status   | OK                                      |
+-------------------------+---------+----------+-----------------------------------------+
2 rows in set (0.076 sec)

MariaDB [classicmodels]> analyze table offices PERSISTENT for COLUMNS(city) indexes ();
+-------------------------+---------+----------+-----------------------------------------+
| Table                   | Op      | Msg_type | Msg_text                                |
+-------------------------+---------+----------+-----------------------------------------+
| classicmodels.customers | analyze | status   | Engine-independent statistics collected |
| classicmodels.customers | analyze | status   | OK                                      |
+-------------------------+---------+----------+-----------------------------------------+
2 rows in set (0.030 sec)

If stats are not used then check the variable USE_STAT_TABLES:

‘never’: Optimizer doesn’t use data from statistics tables. For 10.4 or below default setting.
‘complementary: Optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine.
‘preferably’: data from statistics tables, if it’s not available there, use the data from the storage engine.
‘complementary_for_queries’: Same as complementary, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). From MariaDB 10.4.1.
‘preferably_for_queries’: Same as preferably, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). Available and default from MariaDB 10.4.1.

Refer: https://mariadb.com/kb/en/engine-independent-table-statistics/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.