Fast index creation does not update index statistics
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Undecided
|
Alexey Kopytov | ||
5.1 |
Fix Released
|
Undecided
|
Alexey Kopytov | ||
5.5 |
Fix Released
|
Undecided
|
Alexey Kopytov | ||
5.6 |
Invalid
|
Undecided
|
Unassigned | ||
Rnt-5.1 |
Fix Released
|
Undecided
|
Alexey Kopytov |
Bug Description
Reported upstream as http://
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
*******
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*******
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
*******
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*******
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
*******
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*******
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.
Related branches
- Oleg Tsarev (community): Approve
- Stewart Smith (community): Approve
- Laurynas Biveinis (community): Approve
-
Diff: 105 lines (+91/-0)1 file modifiedpatches/innodb_expand_fast_index_creation.patch (+91/-0)
- Oleg Tsarev (community): Approve
- Laurynas Biveinis (community): Approve
-
Diff: 90 lines (+83/-0)1 file modifiedpatches/innodb_expand_fast_index_creation.patch (+83/-0)
Bug 1026644 is a report about possible regression of this issue.