Bug #19301 Bad and unpredictable optimizer statistics on small InnoDB tables
Submitted: 24 Apr 2006 16:01 Modified: 12 Nov 2010 17:14
Reporter: Oli Sennhauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.21-BK, 5.0.20 OS:Linux (linux)
Assigned to: Heikki Tuuri CPU Architecture:Any

[24 Apr 2006 16:01] Oli Sennhauser
Description:
ANALZYE, OPTIMISE and CHECK generate bad and unpredictable Optimizer Statistics for small InnoDB tables.
This can cause bad execution plans.

How to repeat:
create table b (id int not null auto_increment primary key) engine=innodb;
insert into b values (null);
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
insert into b select null from b;
show index from b;
analyze table b;
show index from b;
analyze table b;
show index from b;
analyze table b;
show index from b;
optimize table b;
show index from b;
optimize table b;
show index from b;
optimize table b;
show index from b;
check table b;
show index from b;
check table b;
show index from b;
check table b;
show index from b;

Suggested fix:
Unknown
[25 Apr 2006 12:24] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.21-BK:

mysql> create table b (id int not null auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into b values (null);
Query OK, 1 row affected (0.00 sec)
...
mysql> insert into b select null from b;
Query OK, 512 rows affected (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |  1030 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> analyze table b;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.b | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |  2029 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> analyze table b;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.b | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |  1015 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> optimize table b;
+--------+----------+----------+----------+
| Table  | Op       | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.b | optimize | status   | OK       |
+--------+----------+----------+----------+
1 row in set (0.06 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |   523 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> optimize table b;
+--------+----------+----------+----------+
| Table  | Op       | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.b | optimize | status   | OK       |
+--------+----------+----------+----------+
1 row in set (0.03 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |  1015 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

mysql> check table b;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.b | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.02 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |  1015 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

mysql> check table b;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.b | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.01 sec)

mysql> show index from b;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| b     |          0 | PRIMARY  |            1 | id          | A         |   400 |     NULL | NULL   |      | BTREE      | NULL    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> select count(id) from b;
+-----------+
| count(id) |
+-----------+
|      1024 |
+-----------+
1 row in set (0.01 sec)
[2 May 2006 15:29] Heikki Tuuri
Maybe this is the well-known problem of 8 random dives.
[8 Nov 2006 14:49] Heikki Tuuri
Marking this as a feature request, as a proper ANALYZE TABLE for InnoDB would fix this.
[12 Nov 2010 17:14] Calvin Sun
This issue has been addressed in the InnoDB plugin, as well as MySQL 5.5.