TransWikia.com

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

Database Administrators Asked on October 28, 2021

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).

One 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.)

Answered by Rick James on October 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP