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.

  • +1 for this good question because Developers and DBAs should pause and think about how index statistics are compiled and stored. Commented Jun 21, 2011 at 16:21
  • For reference, from mysql documentation website: <dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html> > ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing eight random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.
    – Chen Xie
    Commented Jan 24, 2013 at 22:37

3 Answers 3


The direct answer for this would be


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.



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.

  • I updated my answer to highlight the uselessness of ANALYZE TABLE on InnoDB tables. Commented Jun 21, 2011 at 16:15
  • I agree with your answer when you mentioned the variance in cardinality. That's exactly what I was saying when I said cardinality approximations. Commented Jun 21, 2011 at 16:19
  • I also needed mention that using hints in queries is not always the best thing to do when the MySQL Query Optimizer tends to eliminate them at times. Here is a link to what happens internally to queries that can actually make data disappear in parts of query plans : dba.stackexchange.com/questions/1371/… Commented Jun 21, 2011 at 22:00

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.