Bug #11023 Compressed table cardinality is not set

• Submitted: 1 Jun 2005 21:18
• Modified: 5 Aug 2005 10:07
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.12
• OS: Any (all)
• Assigned to: Ingo Strüwing
• CPU Architecture: Any

[1 Jun 2005 21:18] Peter Zaitsev

Description:
After compression table looses cardinality data:

CREATE TABLE `a` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> show index from a;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| a     |          1 | i        |            1 | i           | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Compressed table by

FLUSH TABLES;

myisampack -vb a.MYI
myisamchk -rq a.MYI

Now:

mysql> show index from a;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| a     |          1 | i        |            1 | i           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

As you may see cardinality is changed to "NULL" instead of 6. 

Content of table A may be arbitrary, it just should be large  enough for myisampack to be willing to compress it. 

This finally end up as seriously incorrect plans sellected for compressed tables. 

How to repeat:
See above.

[1 Jun 2005 22:04] Peter Zaitsev

Oh yes,

I skipped one step. You obviously need to run ANALYZE on original table after populating it.

The thing is myisamchk -r  for some reason does not update statistics for compressed table, while it  
normally does for normal table. 

Also ANALYZE table does not work on compressed table saying table is read-only

[1 Jun 2005 22:12] MySQL Verification Team

Verified on current BK source.

[5 Aug 2005 10:07] Ingo Strüwing

If you want to have key statistics after a repair, please use the --analyze or -a option to myisamchk, like so:

myisamchk -rqa a.MYI

This is documented in the reference manual 5.8.4.5. Other Options for myisamchk.