| 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: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S4 (Feature request) |
| Version: | 4.0 | OS: | Any (any) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.