Does MySQL use its index metadata to optimize COUNT(*) queries?

Question

Let's consider a user_actions table with a standard indexed primary key user_action_id.

I want to know the number of user actions up to a certain key (say 100 millions):

SELECT COUNT(*) AS `user_actions_count` 
FROM `user_actions` 
WHERE `user_action_id` < 100000000

In my understanding, the index b-tree has to keep the sub-node counts at each tree node for balancing purpose. Would that information be used to perform such an obvious query or would the engine have to scan the whole index to actually count rows?

As a matter of fact, I encounter unexpected performance issues on that query.

Thanks for your help.

EDIT:

Here is the EXPLAINation of the query:

id  select_type table        type  possible_keys key     key_len ref  rows      Extra
1   SIMPLE      user_actions range PRIMARY       PRIMARY 4       NULL 268537554 Using where; Using index

Answer 1

It has to scan the whole index. Look at the output of:

SHOW INDEX FROM user_actions;

You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.

Answer 2

The query needs to check whether it can "see" each row, based on transaction_isolation_mode. So, there are is no shortcut like you suggest.