Bug #59359 Packed Status Always NULL
Submitted: 7 Jan 2011 18:07 Modified: 7 Jan 2011 18:43
Reporter: James McKelvey Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.41, 5.5.9 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[7 Jan 2011 18:07] James McKelvey
Description:
SHOW INDEX FROM always shows the packed status of an index as NULL, whether it is packed or not. The only way to tell the status is to infer it from the index size, which only works if the table has sufficient rows.

How to repeat:
create database test;

use test;

create table x(value VARCHAR(10),
               index ind(value)) ENGINE=MyISAM, PACK_KEYS=1;

show index from x;

Shows PACKED as NULL.

Note that the value is NULL even for very large populated tables.
[7 Jan 2011 18:22] Valeriy Kravchuk
Indeed, even in current mysql-5.5 this columns still shows NULL, and to find out if pack_keys is set one has to use SHOW TABLE STATUS or SHOW CREATE TABLE:

mysql> show table status like 'x';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| x    | MyISAM |      10 | Dynamic    | 16384 |             20 |      327680 | 281474976710655 |       153600 |         0 |           NULL | 2011-01-07 20:17:44 | 2011-01-07 20:17:44 | 2011-01-07 20:17:44 | latin1_swedish_ci |     NULL | pack_keys=0    |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> alter table x pack_keys=1;
Query OK, 16384 rows affected (0.17 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> show table status like 'x';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| x    | MyISAM |      10 | Dynamic    | 16384 |             20 |      327680 | 281474976710655 |        36864 |         0 |           NULL | 2011-01-07 20:19:40 | 2011-01-07 20:19:40 | 2011-01-07 20:19:40 | latin1_swedish_ci |     NULL | pack_keys=1    |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

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

mysql> show create table x\G
*************************** 1. row ***************************
       Table: x
Create Table: CREATE TABLE `x` (
  `value` varchar(10) DEFAULT NULL,
  KEY `ind` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1
1 row in set (0.00 sec)

If the purpose of PACKED column in SHOW INDEX results is different, or if it is not used any more manual (http://dev.mysql.com/doc/refman/5.5/en/show-index.html) should explain this.
[7 Jan 2011 18:43] James McKelvey
SHOW TABLE STATUS and SHOW CREATE TABLE only show the status of PACK_KEYS. But if PACK_KEYS is DEFAULT, a given index may be packed or unpacked. So I think that SHOW INDEX FROM really should show the proper status.