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: | |
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
[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.