4

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 above two was less than 5 seconds. Why is there such a drastic difference everytime the show index is called ?

This happens only to this table, I checked few bigger tables and they show the same numbers every time they are accessed

FYI count on the table: 

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

Few questions:

  1. Why does the cardinality change so much and does it really matter ?
  2. How important is Optimize Table ? and will it make the queries faster ?
Stewie
  • 3,103
  • 2
  • 24
  • 22

2 Answers2

1

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

InnoDB tends to use some form of search depth approximation (dictated by optimizer_search_depth) which entails diving into indexes guessing at the cardinality.

Try setting off innodb_stats_on_metadata

SET GLOBAL innodb_stats_on_metadata = 0;

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

UPDATE 2012-03-06 11:55 EST

Doing OPTIMIZE TABLE to an InnoDB table is useless because once you do that to attempt to compile index statistics, it tends to get reread again if innodb_stats_on_metadata is still 1. I wrote about this in the 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 just plain old useless for index statistics regeneration in InnoDB.

OPTIMIZE TABLE and ANALYZE TABLE work perfectly for MyISAM only.

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Hmm, So running optimize or analyze won't do me any good ! Is there something similar for InnoDB ? THe queries are drastically slow, EXPLAIN shows very good figures few hundred rows to scan etc, but running the same query is a nightmare. A simple count takes 5 minutes on a 3+ mil rows table. – Stewie Mar 06 '12 at 17:01
0

MySQL determines the cardinality of an index by sampling random pages from the index. Pages have varying record counts and distribution.

For the indexes where the cardinality doesn't change, it is likely that the index fits into a single page, or the pages have even distribution (eg. from optimize table).

If the counts vary greatly, you may consider optimizing the table to re-distribute the records. This will help MySQL select the best index.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • We haven't optimized this table for atleast a year (about 1.5mil+ records) The only problem is that the application is a high availability one and major downtime is dangerous. Downtime during optimization is OK, just don't want to slow things down more as I've seen in the past that optimization actually slowed down many queries – Stewie Mar 06 '12 at 16:55
  • As long as the numbers are in the same ball park, or you're not relying on MySQL to select the best index (between two or more indexes with similar cardinality), then there's nothing to worry about. – Marcus Adams Mar 06 '12 at 16:58