| 2013-07-16 13:31:00 |
Laurynas Biveinis |
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:
--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; |
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; |
|
