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).
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP