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:
None 
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:21] Alexey Kopytov
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.
[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?