MySQL Forums
Forum List  »  MyISAM

Re: How to shrink the size of the index file for a myisam table ?
Posted by: Apachez
Date: April 18, 2006 02:08AM

This is odd...

Without the PACK_KEYS=1 in CREATE TABLE statement gives following output (check the Keyfile length):

myisamchk -dv ./Tbl_Search_Match_d

MyISAM file: ./Tbl_Search_Match_d
Record format: Fixed length
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-04-16 0:39:55
Recover time: 2006-04-16 0:46:11
Status: checked,analyzed,sorted index pages
Data records: 4280683 Deleted blocks: 0
Datafile parts: 4280683 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 51368196 Keyfile length: 66159616
Max datafile length: 51539607550 Max keyfile length: 17179868159
Recordlength: 12

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 3 unique uint24 183 1024 1024
5 4 unsigned long 1
9 4 unsigned long 1

The above is the one without PACK_KEYS=1 in CREATE TABLE. The data in the datafile is ordered by the same way as the primary key is created (col1, col2, col3) by using "ALTER TABLE t1 ORDER BY col1, col2, col3".

Now take a look at the table which has PACK_KEYS=1 in the CREATE TABLE:

myisamchk -dv ./Tbl_Search_Match_test

MyISAM file: ./Tbl_Search_Match_test
Record format: Fixed length
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-04-18 9:14:19
Recover time: 2006-04-18 9:25:06
Status: checked,analyzed,sorted index pages
Data records: 4280683 Deleted blocks: 0
Datafile parts: 4280683 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 51368196 Keyfile length: 66159616
Max datafile length: 51539607550 Max keyfile length: 17179868159
Recordlength: 12

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 3 unique uint24 183 1024 1024
5 4 unsigned long 1
9 4 unsigned long 1

Same size?

According to the manual the default behaviour in mysql is to only pack char columns (char, varchar etc) and not numeric columns. Only when PACK_KEYS=1 is defined for the CREATE TABLE then it will pack the numeric columns aswell.

Just for the record I tried to create another table but this time using PACK_KEYS=0 and filled it with the same data as above and sorted the datafile...

The output:

myisamchk -dv ./Tbl_Search_Match_test2

MyISAM file: ./Tbl_Search_Match_test2
Record format: Fixed length
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-04-18 9:55:41
Recover time: 2006-04-18 10:03:53
Status: checked,analyzed,sorted index pages
Data records: 4280683 Deleted blocks: 0
Datafile parts: 4280683 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 51368196 Keyfile length: 66159616
Max datafile length: 51539607550 Max keyfile length: 17179868159
Recordlength: 12

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 3 unique uint24 183 1024 1024
5 4 unsigned long 1
9 4 unsigned long 1


So why do the indexfile has the same size in both cases, shouldnt it be smaller when using PACK_KEYS=1 ?

Im using 4.1.18-log.

There are 4280683 rows and the top 10 distribution (how many rows which start with the same number there exists which should mean that PACK_KEYS can be used efficient):

SELECT SearchWordId, COUNT(*) AS quantity FROM Tbl_Search_Match_test GROUP BY SearchWordId ORDER BY quantity DESC LIMIT 10;

SearchWordId quantity
------------ --------
45 708664
250 364014
51 345417
198 250822
88 218302
460 154722
76 127004
163 116123
876 72353
1046 63537

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.