From where does the MySQL Query Optimizer read index statistics?

Question

I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.

Answer 1

The direct answer for this would be

information_schema.statistics

mysql> desc information_schema.statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

You could SELECT from that table with

SELECT * FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='mytable';

or see the statistics by doing

SHOW INDEXES FROM mydb.mytable;

Please keep in mind that this table is not always accurate in a write-heavy environment. Periodically you will have to run ANALYZE TABLE against all MyISAM tables that are updated frequently. Otherwise, the MySQL Query Optimizer, which relies on information_schema.statistics, can sometimes make bad choices when developing EXPLAIN plans for queries. Index statistics must be as up-to-date as possible.

ANALYZE TABLE has ABSOLUTELY NO EFFECT against InnoDB tables. All index statistics for InnoDB are computed on demand by means of dives into the BTREE pages. Therefore, when you run SHOW INDEXES FROM against an InnoDB table, the cardinalities displayed are always approximations.

UPDATE 2011-06-21 12:17 EDT

For clarification of ANALYZE TABLE, let me rephrase. Running ANALYZE TABLE on InnoDB tables is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that conclusion as well.

Answer 2

Re:ANALYZE TABLE has ABSOLUTELY NO EFFECT against InnoDB tables.

I am not sure if this statement is true. We have heavily reading & writing innodb tables and when mysql optimizer makes the bad choice, the query's explain output shows bad strategy. and also SHOW INDEXES from an Innodb table shows so much variance in their cardinality values. But running an ANALYZE command on those innodb tables fixes the explain plan and also takes away the variance behavior of cardinality. I don't know if ANALYZE table command on Innodb tables helps all the time or not but in our case, it did help about 99% of the time.

We have completely eliminated the bad choice of mysql optimizer by including the "STRAIGHT_JOIN" in our queries. This forced mysql optimizer not to make bad choices or any choices but just follow JOIN condition of what we defined in the query as is.

Answer 3

ANALYZE TABLE for MyISAM scans the entire table and rebuilds stats, which is saved in (I think) the .MYI file. It is rarely needed.

ANALYZE TABLE for InnoDB does do something -- it does the dive mentioned. The problem is that it may help, may make things worse, or (most likely) won't make any visible difference (except in cardinalities).

Newer versions promise to allow changing the 8 not-so-random probes into (1) more random, (2) letting you change the "8" (there are pros and cons of this!), and (3) saving across restarts.

Bottom line: InnoDB still hasn't gotten it 'right'. Do ANALYZE when you feel like it, but don't hold your breath.

Update

To re-phrase... ANALYZE TABLE has a temporary effect (possibly beneficial, possibly not) on optimizations of InnoDB tables.

"Newer version": Beginning with 5.6.6 (2012) and MariaDB 10.1 (2014), stats are handled much better, and ANALYZE is now (1) less often needed, and (2) more permanent.