MySQL-Why does this query use full index scan?

I'm currently studying DB index. In the MySQL classicmodels db, I don't know why does this query use full index scan.

    select salesRepEmployeeNumber, count(*) 
    from customers 
    where creditLimit > 100000
    group by salesRepEmployeeNumber;

The customers table has clustered index on customerNumber and Non-clustered index on salesRepEmployeeNumber. From what I learned, there is a <key, key of clustered index> in the data entry of the non-clustered tree. Isn't the creditLimit column that exists in the WHERE clause information that can only be known by accessing the data record? If so, shouldn't MySQL query optimizer use full table scan not full index scan?B tree clustered index B tree Non-clustered index Query Execution Plan



Read more here: https://stackoverflow.com/questions/64401756/mysql-why-does-this-query-use-full-index-scan

Content Attribution

This content was originally published by Chaeun Gong at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: