Bug #15225 ANALYZE table doesn't updates index cardinality in temporary tables
Submitted: 24 Nov 2005 14:03 Modified: 8 May 2006 19:12
Reporter: Gleb Paharenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.15 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[24 Nov 2005 14:03] Gleb Paharenko
Description:
ANALYZE table doesn't updates index cardinality in temporary tables, while in MyISAM tables it does.

How to repeat:
mysql> create temporary table a(a int, index(a));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values('1'),('2'),('3'),('4'),('5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from a\G;
*************************** 1. row ***************************
       Table: a
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
mysql> analyze table a;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.a | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from a\G;
*************************** 1. row ***************************
       Table: a
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a(a int, index(a));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values('1'),('2'),('3'),('4'),('5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from a\G;
*************************** 1. row ***************************
       Table: a
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
mysql> analyze table a;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.a | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from a\G;
*************************** 1. row ***************************
       Table: a
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 5
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)

BTW OPTIMIZE table works for temporary tables

Suggested fix:
Fix this or document.
[24 Nov 2005 15:22] Jorge del Conde
Thanks for your bug report.  I was able to repeat this in a fresh clone of 4.1
[10 Dec 2005 13:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58
[17 Apr 2006 12:42] Sergei Glukhov
ok to push
[8 May 2006 19:12] Paul Dubois
Noted in 4.1.20 changelog.

<literal>ANALYZE TABLE</literal> for <literal>TEMPORARY</literal>
tables had no effect. (Bug #15225)