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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
