Does the index in InnoDB and MyISAM keep amount of child nodes in B-trees ?
If I have an b-tree index on (field1, field2) and we are dealing with MyISAM (to avoid issues with transactions, locking, etc), why not to store amount of child nodes on transition from one node into the child one in b-tree index ?
Here is a real-case sample:
Environment: MariaDB 10.6.7 Table Engine: tested on both InnoDB and MyISAM
I have a table with some fields, there are about 7 million rows. Now, I'm adding two indexes:
ALTER TABLE `table` ADD KEY `testKey1` (`country`); ALTER TABLE `table` ADD KEY `testKey2` (`country`, `saleTimestamp`);
Then, I run a query:
SELECT COUNT(*) FROM `table` WHERE `country` = 'us'; +----------+ | COUNT(*) | +----------+ | 5411930 | +----------+ 1 row in set (1.569 sec)
Explain shows what it uses index
testKey2, which makes sense for me.
Question: how come that index doesn't have number of children ? So to find how many records we have to scan a lot of records.
I was just expecting that the index (
country) would keep amount of child records (like in a tree, the amount of child nodes in some node), so if I ask how many records under the specific country, the MariaDB would simply get this information from the index.
Am I missing something ?
Thanks for helping