Hi, I have a table that needs to be indexed heavily. Even though I specify the `pack_keys=1` table option and all the keys are combinations of unsigned integers of various sizes, I don't really get any key packing - read the full story below. Is there any other magic I should use to get my keys packed? Jan The Tale of Keys that Don't Pack -------------------------------- I'm running MySQL 3.23.32 installed from the Linux RPM by TCX. The create statement for my table: CREATE TABLE `F_PolozkaVysledovkyMesic` ( # foreign keys to dimensions, all together this is the semantic key `id_SoustavaStredisek` smallint(5) unsigned NOT NULL default '0', `id_RadekVysledovky` tinyint(3) unsigned NOT NULL default '0', `id_Obdobi` tinyint(3) unsigned NOT NULL default '0', `id_Modus` tinyint(3) unsigned NOT NULL default '0', # value of the fact `value` decimal(10,1) NOT NULL default '0.0', # the effective range, foreign keys to a table `id_OdTransakce` int(10) unsigned NOT NULL default '1', `id_DoTransakce` int(10) unsigned NOT NULL default '4294967295', # indexing: # the primary key is the semantic key plus the valid_from PRIMARY KEY (`id_SoustavaStredisek`,`id_Modus`,`id_RadekVysledovky`,`id_Obdobi`,`id_OdTransakce`), # a candidate key names the valid_to first, then the semantic key UNIQUE KEY `id_DoTransakce`(`id_DoTransakce`,`id_SoustavaStredisek`,`id_Modus`,`id_RadekVysledovky`,`id_Obdobi`) ) TYPE=MyISAM PACK_KEYS=1; After I create the table, the index listing doesn't look too optimistic, mysql> show index from F_PolozkaVysledovkyMesic; +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | | Collation | Cardinality | Sub_part | Packed | +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 1 | |id_SoustavaStredisek | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 2 | id_Modus | | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 3 | |id_RadekVysledovky | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 4 | id_Obdobi | | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 5 | |id_OdTransakce | A | 0 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 1 | |id_DoTransakce | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 2 | |id_SoustavaStredisek | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 3 | id_Modus | | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 4 | |id_RadekVysledovky | A | NULL | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 5 | id_Obdobi | | A | 0 | NULL | NULL | +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ And after I load it with my sample data and do a 'repair', I can see the following: mysql> show index from F_PolozkaVysledovkyMesic; +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | | Collation | Cardinality | Sub_part | Packed | +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 1 | |id_SoustavaStredisek | A | 23 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 2 | id_Modus | | A | 73 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 3 | |id_RadekVysledovky | A | 673 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 4 | id_Obdobi | | A | 86175 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | PRIMARY | 5 | |id_OdTransakce | A | 86175 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 1 | |id_DoTransakce | A | 1 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 2 | |id_SoustavaStredisek | A | 23 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 3 | id_Modus | | A | 73 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 4 | |id_RadekVysledovky | A | 673 | NULL | NULL | | F_PolozkaVysledovkyMesic | 0 | id_DoTransakce | 5 | id_Obdobi | | A | 86175 | NULL | NULL | +--------------------------+------------+----------------+--------------+----------------------+-----------+-------------+----------+--------+ So, no key packing is taking place. If it were, it would help greatly, as one can see from the above statistics. Now the index file actually takes up more space than the data file, mysql> show table status like 'F_PolozkaVysledovkyMesic'; +--------------------------+--------+------------+-------+----------------+-------------+--------------+-----------------+ | Name | Type | Row_format | Rows | Avg_row_length | |Data_length | Index_length | Create_options | +--------------------------+--------+------------+-------+----------------+-------------+--------------+-----------------+ | F_PolozkaVysledovkyMesic | MyISAM | Fixed | 86175 | 26 | |2240550 | 2302976 | pack_keys=1 | +--------------------------+--------+------------+-------+----------------+-------------+--------------+-----------------+ (I've selected just the important columns from the output.) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
