Engine-independent table statistics

This is a preliminary documentation about engine-independent table statistics.

Introduction

Before this project, MySQL/MariaDB optimizer relied on storage engines (e.g. InnoDB) to provide statistics for the query optimizer. This approach worked, however it had some deficiencies:

• Storage engines provided poor statistics (this fixed to some extent in MySQL 5.6)
• The statistics was supplied through the MySQL Storage Engine Interface, which puts a lot of restrictions on what kind of data is supplied (for example, there is no way to get any data about value distribution in a non-indexed column)
• There was little control of the statistics. There was no way to "pin" current statistic values, or provide some values on your own, etc.

This project is about lifting these limitations.

• Statistics is stored in regular tables in mysql database.
  • it is possible for DBA to read and update the values
• More data is collected/used.

Currently, statistics data these three tables:

+--------------------------+
| Tables_in_mysql (%stat%) |
+--------------------------+
| column_stat              |
| index_stat               |
| table_stat               |
+--------------------------+

use/update of data from these tables is controlled by @@use_stat_tables variable. Possible values are

• 'never'
• 'preferably'
• 'complementary'

Contents of statistics tables:

index_stat table

table_stat table

db_name table_name cardinality

column_stat table

+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency