Description:
Index statistics (cardinality) do not seem to update properly with temporary tables in 4.0.24. (They update correctly in 4.1.9.) This can cause a very poor execution plan without doing bizarre workarounds.
How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a VARCHAR(10) );
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
-- Index statistics updated without needing ANALYZE
CREATE TEMPORARY TABLE t2 ( index (a(10)) ) SELECT a FROM t1 GROUP BY a;
SHOW INDEX FROM t2;
DROP TABLE t1, t2;
-- Index statistics do not update after ANALYZE with TEMPORARY table,
-- but DO update after ANALYZE + SHOW CREATE
CREATE TABLE t1 ( a VARCHAR(20) );
INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five');
CREATE TEMPORARY TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a;
SHOW INDEX FROM t2;
ANALYZE TABLE t2;
SHOW INDEX FROM t2;
SHOW CREATE TABLE t2;
SHOW INDEX FROM t2;
DROP TEMPORARY TABLE t2;
-- Index statistics DO update after OPTIMIZE with TEMPORARY TABLE
CREATE TEMPORARY TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a;
SHOW INDEX FROM t2;
OPTIMIZE TABLE t2;
SHOW INDEX FROM t2;
DROP TEMPORARY TABLE t2;
-- Index statistics do update after ANALYZE with regular table,
-- but ANALYZE is necessary.
CREATE TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a;
SHOW INDEX FROM t2;
ANALYZE TABLE t2;
SHOW INDEX FROM t2;
DROP TABLE t1, t2;
Suggested fix:
n/a
Description: Index statistics (cardinality) do not seem to update properly with temporary tables in 4.0.24. (They update correctly in 4.1.9.) This can cause a very poor execution plan without doing bizarre workarounds. How to repeat: DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 ( a VARCHAR(10) ); INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); -- Index statistics updated without needing ANALYZE CREATE TEMPORARY TABLE t2 ( index (a(10)) ) SELECT a FROM t1 GROUP BY a; SHOW INDEX FROM t2; DROP TABLE t1, t2; -- Index statistics do not update after ANALYZE with TEMPORARY table, -- but DO update after ANALYZE + SHOW CREATE CREATE TABLE t1 ( a VARCHAR(20) ); INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); CREATE TEMPORARY TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a; SHOW INDEX FROM t2; ANALYZE TABLE t2; SHOW INDEX FROM t2; SHOW CREATE TABLE t2; SHOW INDEX FROM t2; DROP TEMPORARY TABLE t2; -- Index statistics DO update after OPTIMIZE with TEMPORARY TABLE CREATE TEMPORARY TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a; SHOW INDEX FROM t2; OPTIMIZE TABLE t2; SHOW INDEX FROM t2; DROP TEMPORARY TABLE t2; -- Index statistics do update after ANALYZE with regular table, -- but ANALYZE is necessary. CREATE TABLE t2 ( index (a(20)) ) SELECT a FROM t1 GROUP BY a; SHOW INDEX FROM t2; ANALYZE TABLE t2; SHOW INDEX FROM t2; DROP TABLE t1, t2; Suggested fix: n/a