Comment 6 for bug 1484311

Revision history for this message
Yura Sorokin (yura-sorokin) wrote :

Here is a simplified test case (1 field, 1 index and 1 INSERT SELECT)

******************************************************
--source include/have_innodb.inc

CREATE TABLE t1(f1 INT, key(f1)) STATS_PERSISTENT=1 ENGINE=InnoDB;
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6);

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

INSERT INTO t1(f1) SELECT f1 FROM t1;

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

DROP TABLE t1;
******************************************************

After the 'INSERT INTO t1(f1) SELECT f1 FROM t1' statement index cardinality changes from expected 6 to 12.