Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Posted on

Question :

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes?

If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate index exists).

Answer :

I am not 100% sure about the first part, but I would say NO – if it did, it would have to update the counts all the way to root on each write, which would mean writing more pages to disk and syncing that. When you add/remove the leaf node, then in the good case you just need to write that one page to disk, and with this you would have to update all those “parents” – say 2-5 more pages depending on the number of rows in the table.

The other thing is that InnoDB cannot use that information anyway in queries. When you run the COUNT, you run it in some transaction, that usually means “repeatable reads” mode (but similar reasons will apply to other modes). So the query has to check the visibility of each row/record in the current transaction scope. And you cannot keep separate count per active transaction in the b-tree.

Leave a Reply

Your email address will not be published. Required fields are marked *