Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
+1 for this good question because Developers and DBAs should pause and think about how index statistics are compiled and stored. –  RolandoMySQLDBA Jun 21 '11 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 Jan 24 '13 at 22:37
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I updated my answer to highlight the uselessness of ANALYZE TABLE on InnoDB tables. –  RolandoMySQLDBA Jun 21 '11 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. –  RolandoMySQLDBA Jun 21 '11 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/… –  RolandoMySQLDBA Jun 21 '11 at 22:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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