Wrong cardinality estimate on a re-created table after a fully-scanning ANALYZE TABLE
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Invalid
|
Undecided
|
Unassigned | ||
5.1 |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Triaged
|
Medium
|
Unassigned | ||
5.6 |
Invalid
|
Undecided
|
Unassigned |
Bug Description
If a table is created, populated, and dropped, and then recreated and re-populated, the index cardinality estimates will differ, even after innodb_
--source include/
SET @@GLOBAL.
SET @@GLOBAL.
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, '');
INSERT INTO t1 VALUES(2, '');
DELETE FROM t1 WHERE id = 2;
ANALYZE TABLE t1;
SHOW INDEXES IN t1; # Cardinality == 1
DROP TABLE t1;
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, '');
INSERT INTO t1 VALUES(2, '');
DELETE FROM t1 WHERE id = 2;
ANALYZE TABLE t1;
SHOW INDEXES IN t1; # Cardinality == 2
DROP TABLE t1;
SET @@GLOBAL.
SET @@GLOBAL.
tags: | added: xtradb |
Percona Server 5.5 output:
SET @@GLOBAL. innodb_ stats_sample_ pages=30000; innodb_ stats_on_ metadata= OFF; innodb_ stats_sample_ pages=default; innodb_ stats_on_ metadata= default;
SET @@GLOBAL.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, '');
INSERT INTO t1 VALUES(2, '');
DELETE FROM t1 WHERE id = 2;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SHOW INDEXES IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 id A 1 NULL NULL BTREE
DROP TABLE t1;
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, '');
INSERT INTO t1 VALUES(2, '');
DELETE FROM t1 WHERE id = 2;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
SHOW INDEXES IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 id A 2 NULL NULL BTREE
DROP TABLE t1;
SET @@GLOBAL.
SET @@GLOBAL.