List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 30 2005 9:05pm
Subject:Re: PACK_KEYS not packing keys?
View as plain text  
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
	dnelson@stripped
Thread
PACK_KEYS not packing keys?Julian Pellico30 Mar
  • Re: PACK_KEYS not packing keys?Dan Nelson30 Mar
Re: PACK_KEYS not packing keys?Julian Pellico31 Mar