| Bug #62516 | Fast index creation does not update index statistics | ||
|---|---|---|---|
| Submitted: | 23 Sep 2011 16:21 | Modified: | 23 Sep 2011 16:24 |
| Reporter: | Alexey Kopytov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1, 5.5 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[23 Sep 2011 16:24]
Valeriy Kravchuk
Thank you for the bug report. Verified with 5.5.17 on Mac also:
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.17-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> create table t1(id int not null auto_increment primary key, a char(1)
-> not null, b char(36) not null) engine=innodb;
Query OK, 0 rows affected (0.92 sec)
mysql> insert into t1(a,b) values ('a','b');
Query OK, 1 row affected (0.18 sec)
mysql> insert into t1(a,b) select a,b from t1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1(a,b) select a,b from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1(a,b) select a,b from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(a,b) select a,b from t1;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> alter table t1 add key (a);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select count(*) from t1, t1 t2 where t1.a = t2.a and
-> t1.b = t2.b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: a
key: a
key_len: 1
ref: test.t1.a
rows: 1
Extra: Using where
2 rows in set (0.06 sec)
mysql> analyze table t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> explain select count(*) from t1, t1 t2 where t1.a = t2.a and t1.b = t2.b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra: Using where; Using join buffer
2 rows in set (0.03 sec)
[18 Sep 2012 10:42]
Marko Mäkelä
This bug has been addressed in MySQL 5.6, as part of the new ALTER TABLE API (WL#5534, WL#5526). Theoretically, the fix could be backported to 5.1 plugin and 5.5.
[2 Aug 2013 3:32]
Laurynas Biveinis
The testcase does not fail anymore on 5.5.33. Has this issue been fixed?
Description: After adding an index using fast index creation, statistics for that index provided by InnoDB is left in a bogus state until an explicit ANALYZE TABLE is executed. This is not the case for the old method of adding indexes by copying rows used by builtin InnoDB. How to repeat: drop table if exists t1; create table t1(id int not null auto_increment primary key, a char(1) not null, b char(36) not null) engine=innodb; insert into t1(a,b) values ('a','b'); insert into t1(a,b) select a,b from t1; insert into t1(a,b) select a,b from t1; insert into t1(a,b) select a,b from t1; insert into t1(a,b) select a,b from t1; alter table t1 add key (a); Builtin InnoDB in 5.1: mysql> explain select count(*) from t1, t1 t2 where t1.a = t2.a and t1.b = t2.b\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16 Extra: Using where; Using join buffer 2 rows in set (0.01 sec) InnoDB plugin 1.0.17: mysql> explain select count(*) from t1, t1 t2 where t1.a = t2.a and t1.b = t2.b\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: a key: NULL key_len: NULL ref: NULL rows: 16 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: a key: a key_len: 1 ref: test.t1.a rows: 1 Extra: Using where 2 rows in set (0.00 sec) mysql> analyze table t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t1, t1 t2 where t1.a = t2.a and t1.b = t2.b\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: a key: NULL key_len: NULL ref: NULL rows: 16 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: a key: NULL key_len: NULL ref: NULL rows: 16 Extra: Using where; Using join buffer 2 rows in set (0.01 sec) Suggested fix: Update statistics after fast index creation.