1

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
  • see what EXPLAIN shows you – nbk Oct 14 at 20:50
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.

| improve this answer | |
  • Thanks for the answer, even if a bit disappointed! I'm probably going to use the max action_id < 1G to have a very raw approximation of the auto-incremented primary key. That value is a logarithmic fetch from the index. – Tom Desp Oct 15 at 10:09
1

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.

| improve this answer | |

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.