1

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

2

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.

  • 1
    you're right. There is no rows count in B+ tree – akuzminsky Oct 16 '15 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.