Using engine-independent table statistics

MariaDB includes a facility to gather statistics on all tables, no matter what storage engine those tables use. The MariaDB optimizer can use these statistics to better calculate the optimum query plans.

How to do it...

1. Connect to MariaDB using the mysql command-line client with a user that has the SUPER privilege.

2. Run the following command:

   SET GLOBAL use_stat_tables=complementary;
   

3. Force an update of the table statistics for a table with the following command (change table_name to the name of an existing table):

   ANALYZE TABLE table_name;
   

4. View the collected table, index, and column statistics with the following commands:

   SELECT * FROM mysql.table_stats;
   SELECT * FROM mysql.index_stats;
   SELECT * FROM mysql.column_stats;
   

How it works...

How MariaDB uses the engine-independent table statistics is controlled by the use_stat_tables variable. There are three valid values: never means that MariaDB will not use the statistics, complementary means that MariaDB...