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