Skip navigation links

MySQL Newsletter - Subscribe Today!

How Calculate btree Index Depth

Newsletters older than 6 months may have links that are out of date. Please use the Search to check for updated links.

How can you calculate btree index depth?

Start with myisamchk -dvv table_file
....
Data records: 405
....
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
....
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique unsigned long 1 7168 1024
2 6 100 unique char packed stripped NULL 0 4096 1024
261 100 char stripped NULL 0
....

So - Blocksize (for key) - 1024.

Key length is (key length)+(Datafile pointer)+(Keyfile pointer). Keyfile pointer is omitted in leaf pages, but let's disregard it for simplicity.

So - 405 entries, 4+4+4=12 entry size, 1024 page size. Depth is log(405)/log(1024/12) = 1.35

The second key is not that easy. 100+100 is maximal length, but keys are space stripped and packed, so actual length is much less. Anyway, depth is not bigger than log(405)/log(1024/208) < 4.