Fast index creation does not update index statistics

Reported by Alexey Kopytov on 2011-09-23
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.6
5.1
Undecided
Alexey Kopytov
5.5
Undecided
Alexey Kopytov
5.6
Undecided
Unassigned
Rnt-5.1
Undecided
Alexey Kopytov

Bug Description

Reported upstream as http://bugs.mysql.com/bug.php?id=62516

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.

Related branches

lp:~akopytov/percona-server/bug857590-5.1
Merged into lp:percona-server/5.1 at revision 296
Oleg Tsarev (community): Approve on 2011-09-27
Stewart Smith (community): Approve on 2011-09-27
Laurynas Biveinis: Approve on 2011-09-26
lp:~akopytov/percona-server/bug857590-5.5
Merged into lp:percona-server/5.5 at revision 160
Oleg Tsarev (community): Approve on 2011-09-27
Laurynas Biveinis: Approve on 2011-09-26

Bug 1026644 is a report about possible regression of this issue.

Alexey Kopytov (akopytov) wrote :

Does not affect PS 5.6, as the problem has been addressed in 5.6 upstream.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.