List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 1 2000 7:17pm
Subject:Some questions about 3.23
View as plain text  


> 1) As I read the manual it says that PACK_KEYS will pack keys which will =
> make inserts slower - does enyone has information how much slower ?  =

Sorry, but this depends totally on your hardware, disk speed,
distribution on keys, what types on keys and how you are going to use
the table. Things may get 50 % faster to 50 % slower...  The easyest
way to test this to modify the MySQL benchmarks to match your application.

> Other question is which is default ? PACK_KEYS=3D0 ?  Other interesting =
> thing is how much can compression give ? I tried ang got just 5-20% size =
> decrease on files with indexes on multiple INT columns.

I thought we had documented this already :(  Anyhow, I just added the
following to the manual:

If you don't use ''ACK_KEYS, the default is to only pack strings,
not numbers.  If you use 'PACK_KEYS', even numbers will be packed.

When packing binary number keys, MySQL will use prefix compression.
This means that you will only get a big benefit of this if you have
many numbers that are the same.  Prefix compression means that every
key needs one extra byte to tell how many bytes of the previous key is
the same for the next key (note that 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 rows
in a row, all following 'same' keys will usually only take 2 bytes
(including the pointer to the row).  Compare this to the ordinary case
where the following keys will take 'storage_size_for_key' +
pointer_size (usually 4).  On the other hand, if all keys are
totally different, you will loose 1 byte per key, if the key isn't a
key that can have NULL values (In this case the packed key length will
be stored in the same byte that is used to mark if a key is NULL)


> 2) Read in manual about MAX_ROWS option in table create syntax but I'm =
> wondering how certan should it be - is it like if tablesize =
> (ROW_LENGTH*MAX_ROWS) does not cross 4GB limit it will use 32Bit data =
> pointers in other case 63Bit pointers will be used or pointers can be =
> 32bit 48bit etc  depending on expected tablesize ?

The pointer size for keys / rows can be of sizes 2-8, depending on the
table size.

> 3) If I'll compile MYSQL with --with-big-tables option will it allow me =
> to create tables with small data pointers for some needs ?

Yes.  The default is always that the table will be up to 4G.  (4 byte
row pointers and probably 3 byte key pointers;  The key pointer is always
multiplied with 1024, which gives it a much bigger range than you
would ordinary get with 3 bytes).

Some questions about 3.23Peter Zaitsev1 Jan
  • Some questions about 3.23Michael Widenius2 Jan