Crazy counting power for the status of the displayed table

Question

Iteration1:

mysql> show table status LIKE "mybigusertable"; +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------+ | mybigusertable | InnoDB | 10 | Compact | 3089655 | 1686 | 5209325568 | 0 | 797671424 | 0 | 3154997 | 2011-12-04 03:46:43 | NULL | NULL | utf8_unicode_ci | NULL | | InnoDB free: 13775872 kB | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------+ mysql> show index from mybigusertable; +-----------------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | mybigusertable | 0 | PRIMARY | 1 | someid | A | 3402091 | NULL | NULL | | BTREE 

Iteration 2

 mysql> show index from mybigusertable; +-----------------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------+------------+-----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | mybigusertable | 0 | PRIMARY | 1 | someid | A | 2811954 | NULL | NULL | | BTREE 

The above time between the two above was less than 5 seconds. Why is there such a radical difference every time the show index is called?

This only happens with this table, I checked several large tables and they show the same numbers every time I access them

FYI number in table:

 mysql> select count(*) from mybigusertable; +----------+ | count(*) | +----------+ | 3109320 | +----------+ 1 row in set (4 min 34.00 sec) 

A few questions:

• Why does power change so much, and does it really matter?
• How important is Optimize a table ? and will he make requests faster?

Answer 1

MySQL determines the power of an index by fetching random pages from the index. Pages have different accounts and distribution.

For indexes where the power does not change, it is likely that the index fits into one page or the pages have a uniform distribution (for example, from the optimization table).

If the counts are very different, you might consider optimizing the table to redistribute the records. This will help MySQL choose the best index.

Answer 2

I think the problem is how the table metadata is handled for InnoDB.

InnoDB tends to use some form of approximation of search depth (dictated by optimizer_search_depth ), which entails immersion in indexes of fortune telling.

Try setting innodb_stats_on_metadata strong>

 SET GLOBAL innodb_stats_on_metadata = 0; 

This will help to read metadata faster and stabilize query execution plans.

UPDATE 2012-03-06 11:55 EST

Running the OPTIMIZE TABLE table in the InnoDB table is useless, because as soon as you do this to try to compile the index statistics, it will be reread again if innodb_stats_on_metadata is still 1. I wrote about this in DBA StackExchange back in June 2011.

UPDATE 2012-03-06 11:59 EST

OK Since you are using MySQL 5.0.77, then OPTIMIZE TABLE is simply useless for restoring index statistics in InnoDB.

OPTIMIZE TABLE and ANALYZE TABLE work fine only for MyISAM.