List:General Discussion« Previous MessageNext Message »
From:Julian Pellico Date:March 30 2005 7:42pm
Subject:PACK_KEYS not packing keys?
View as plain text  
Hi,
I tried searching for this problem, but I couldn't find any reference
to it, so here goes...

using mysql 4.0.23, I created two tables, one of them whose key is
packed. My goal is to see the effect on the size of the index file.

mysql> show create table Unpacked;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                             
                                                                      
                           |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Unpacked | CREATE TABLE `Unpacked` (
  `key_` int(10) unsigned NOT NULL auto_increment,
  `value` int(10) unsigned default NULL,
  PRIMARY KEY  (`key_`)
) TYPE=MyISAM |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table Packed;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                               
                                                                      
                                   |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Packed | CREATE TABLE `Packed` (
  `key_` int(10) unsigned NOT NULL auto_increment,
  `value` int(10) unsigned default NULL,
  PRIMARY KEY  (`key_`)
) TYPE=MyISAM PACK_KEYS=1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

Here's the core of a perl script that inserted 100K rows:

for (my $i = 0; $i < 100000; $i++) { $dbh->do("INSERT INTO Packed
(value) VALUES (" . int(rand(1000000)) . ")") or die "bah " .
$dbh->err; }

Here's the size of the files after all's said and done:

-rw-rw----  1 mysql  users  900000 Mar 30 11:18 Packed.MYD
-rw-rw----  1 mysql  users  821248 Mar 30 11:18 Packed.MYI
-rw-rw----  1 mysql  users    8580 Mar 30 11:17 Packed.frm
-rw-rw----  1 mysql  users  900000 Mar 30 11:17 Unpacked.MYD
-rw-rw----  1 mysql  users  821248 Mar 30 11:18 Unpacked.MYI
-rw-rw----  1 mysql  users    8580 Mar 30 11:17 Unpacked.frm

If I understand correctly, PACK_KEYS should reduce the size of the
index file. Does the fact that SHOW INDEX on Packed shows packed=NULL
indicate something's wrong? Does there need to be more variation in
the values of the key? (I would think that pack_keys helps most when
keys are very similar)

Thanks,
Julian
Thread
PACK_KEYS not packing keys?Julian Pellico30 Mar
  • Re: PACK_KEYS not packing keys?Dan Nelson30 Mar
Re: PACK_KEYS not packing keys?Julian Pellico31 Mar