In the last episode (Mar 30), Julian Pellico said:
> 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.
>
> 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
Primary keys won't pack well, since there's little redundancy to pack
out. From the manual (under CREATE TABLE Syntax):
When packing binary number keys, MySQL uses prefix compression:
* Every key needs one extra byte to indicate how many bytes of the
previous key are the same for the next key.
* The pointer to the row is stored in high-byte-first order
directly after the key, to improve compression.
This means that if you have many equal keys on two consecutive rows,
all following ``same'' keys usually only take two bytes (including
the pointer to the row). Compare this to the ordinary case where the
following keys takes storage_size_for_key + pointer_size (where the
pointer size is usually 4). Conversely, you get a big benefit from
prefix compression only if you have many numbers that are the same.
If all keys are totally different, you use one byte more per key, if
the key isn't a key that can have NULL values. (In this case, the
packed key length is stored in the same byte that is used to mark if
a key is NULL.)
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.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
