Fast index creation does not update index statistics

Bug #857590 reported by Alexey Kopytov
16
This bug affects 3 people
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://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.

Tags: cr i18817

Related branches

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

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

Revision history for this message
Alexey Kopytov (akopytov) wrote :

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

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2686

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

Other bug subscribers

Remote bug watches

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