Thanks Dan,
I ran myisamchk as you suggested. The detailed results are below, but
in summary there's no difference between the two indices. Perhaps
mysql doesn't want to pack the key for some reason? I'm running
FreeBSD 4.1.
Checking MyISAM file: /home/y/var/mysql/data/Test/Unpacked.MYI
Data records: 100000 Deleted blocks: 0
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 3
Total: Keyblocks used: 98% Packed: 0%
- check records and index references
Records: 100000 M.recordlength: 9 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 100000 Delete blocks: 0
Record data: 900000 Deleted data: 0
Lost space: 0 Linkdata: 0
MyISAM-table '/home/y/var/mysql/data/Test/Unpacked.MYI' is usable but
should be fixed
User time 0.25, System time 0.00
Maximum resident set size 3044, Integral resident set size 580664
Non-physical pagefaults 660, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 9 out 0, Signals 0
Voluntary context switches 8, Involuntary context switches 135
Checking MyISAM file: /home/y/var/mysql/data/Test/Packed.MYI
Data records: 100000 Deleted blocks: 0
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 3
Total: Keyblocks used: 98% Packed: 0%
- check records and index references
Records: 100000 M.recordlength: 9 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 100000 Delete blocks: 0
Record data: 900000 Deleted data: 0
Lost space: 0 Linkdata: 0
MyISAM-table '/home/y/var/mysql/data/Test/Packed.MYI' is usable but
should be fixed
User time 0.24, System time 0.01
Maximum resident set size 3052, Integral resident set size 666608
Non-physical pagefaults 660, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 9 out 0, Signals 0
Voluntary context switches 8, Involuntary context switches 141
If anyone has any ideas about this....please let me know.
- Julian
>Now from this description, I would have expected a 2-byte savings per
>record (since the three most-significant bytes of each key should get
>compressed out, but you lose one byte to store the same-bytes value,
>for a total savings of 2). Try running "myisamchk -eis" on each table
>and see what the usage and packing percentages are for each index. It
>may be that your 'packed' table is using the same number of keyblocks,
>just less-densely filled.
