Bug #7519 Index statistics do not seem to update properly
Submitted: 24 Dec 2004 0:24 Modified: 3 Mar 2005 17:41
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.24 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[24 Dec 2004 0:24] Dean Ellis
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
[28 Feb 2005 11:59] Sergey Petrunya
Fix approved by Igor
[28 Feb 2005 15:44] Sergey Petrunya
Fixed in 4.0.24
[3 Mar 2005 17:41] Paul DuBois
Noted in 4.0.24 changelog.