Leyne, Sean (who is usually very reliable) wrote:

>
>> Ok, so... as I understood, as far as the max index depths I have is 3,
>> there is no need to use a page size above 4k.
>
> Actually, you SHOULD increase the page size. A depth of 3 is not good.
>

Err, an index depth of three generally performs pretty well, in my
experience. Getting the depth down to two is hard with a large table.
Every index entry is at least 5 bytes long - four for the page or
record number plus one byte to indicate that this is a duplicate of
the previous key value. Lets assume an average data length of 3 bytes
after compression, making each index node 8 bytes long. So, in round
numbers, considering overhead, the number of leaf entries in an index
looks like this:

page size 1 level 2 level 3 level

4 KB 500 250,000 12,500,000
8 KB 1000 1,000,000 1,000,000,000
16 KB 2000 4,000,000 8,000,000,000

A three-level index on a 4KB page will have 501 upper level index
pages when its completely full. Keeping 501 pages in cache isn't
hard - even keeping the full upper levels of an 8K page or a 16K
page is feasible.

Regards,


Ann