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

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.

Thanks for your bug report.  I was able to repeat this in a fresh clone of 4.1

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

ok to push

Noted in 4.1.20 changelog.

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

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.