What is branching factor of mysql myisam's b-tree

Question

I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node).
I also would like to know how the char/varchar keys are stored - are they stored in full length or just first "meaningful" characters.

By "meaningful" characters I mean:
create table a(
   name varchar(1000) primary key
);

insert into a('london');

"london" key is 1000 characters long (assuming ASCII), but the key
itself is just 6 characters (7 with null character, if we represent it as C-string).

Rick James · Answer

There is about 8 bytes of overhead per index entry.  So, if the key is typically 6 bytes, and the key_buffer block size is 1KB, and the BTree is 69% full:
1024 * .69 / (8+6)

says there will be about 50 rows.
So, 3 levels of BTree can handle up to 50^3 = 125K rows.
The .69 comes from assuming lots of random inserts -- leading to each block oscillating between 100% full just before a block split and 50% after.
There may be an obscure SELECT against the information_schema to get the exact value for the indexes.  (I know of one for InnoDB.)

What is branching factor of mysql myisam's b-tree

One Answer

Add your own answers!

Ask a Question