Bug #3733 ALTER TABLE doesn't restore cardinality values on MyISAM indexes
Submitted: 12 May 2004 22:55 Modified: 3 Aug 2004 15:20
Reporter: Timothy Smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:4.0 OS:Any (any)
Assigned to: CPU Architecture:Any

[12 May 2004 22:55] Timothy Smith
Description:
When ALTER TABLE makes a new table, the cardinality for various indexes may or may not be calculated.  Right now, it's necessary to run ANALYZE TABLE to be sure of the cardinality values.

But if repair_by_sort is used, then cardinality for those indexes *is* updated, so it is a waste to re-calculate those values.

Also, it is surprising to the user to find that, after simply adding a new column to a table (for example), the cardinality on a primary key has been reset to NULL.

How to repeat:
Notice that the cardinality for the first key part NULL after the ALTER TABLE.  It would be nice if it were 250, like before....

mysql> analyze table foo;
+----------+---------+----------+-----------------------------+
| Table    | Op      | Msg_type | Msg_text                    |
+----------+---------+----------+-----------------------------+
| test.foo | analyze | status   | Table is already up to date |
+----------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> show indexes from foo\G
*************************** 1. row ***************************
       Table: foo
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 250
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: foo
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 2
 Column_name: b
   Collation: A
 Cardinality: 1000
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

mysql> alter table foo add d char(5);
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> show indexes from foo\G
*************************** 1. row ***************************
       Table: foo
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: foo
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 2
 Column_name: b
   Collation: A
 Cardinality: 1000
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

mysql>

Suggested fix:
Add an option for ALTER TABLE to calculate index statistics on the new table?  Do this as an integral part of ALTER TABLE?  I don't know what would make sense from the server's perspective.
[13 May 2004 9:46] Sergei Golubchik
it will happen automatically when we'll do WL#1333
[3 Aug 2004 13:38] Miguel Dias
If one does two alter tables in a row, with this bug, we actually loose rows from the original table.

I think the priority could be revised or the documentation updated to reflect a warning.
[3 Aug 2004 15:20] Sergei Golubchik
If you *loose rows* it's really a bug, please create a separate entry for this (as a confirmation, that it's indeed what happens) and try to provide a repeatable test case - we'll fix it.

But it's not related to this entry, which talsk about "loosing cardinality values" - something you can observe with SHOW KEYS.

Alter table is not expected to calculate cardinality, sometimes it does it as a by-product, sometimes it does not.