Wrong cardinality estimate on a re-created table after a fully-scanning ANALYZE TABLE

Bug #1201802 reported by Laurynas Biveinis
10
This bug affects 2 people
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_stats_sample_pages to a large value and running explicit ANALYZE TABLE. This is a Percona Server-specific bug.

--source include/have_innodb.inc

SET @@GLOBAL.innodb_stats_sample_pages=30000;
SET @@GLOBAL.innodb_stats_on_metadata=OFF;

--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.innodb_stats_sample_pages=default;
SET @@GLOBAL.innodb_stats_on_metadata=default;

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

Percona Server 5.5 output:

SET @@GLOBAL.innodb_stats_sample_pages=30000;
SET @@GLOBAL.innodb_stats_on_metadata=OFF;
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.innodb_stats_sample_pages=default;
SET @@GLOBAL.innodb_stats_on_metadata=default;

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

MySQL 5.5 output:

SET @@GLOBAL.innodb_stats_sample_pages=30000;
SET @@GLOBAL.innodb_stats_on_metadata=OFF;
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 2 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.innodb_stats_sample_pages=default;
SET @@GLOBAL.innodb_stats_on_metadata=default;

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

MySQL 5.1, 5.5, 5.6, PS 5.1 and 5.6 are OK.

tags: added: xtradb
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-2990

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.