MySQL-Why does this query use full index scan?

Question

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

Is this a typo? salesRepEmployeeNumcustomersber

Rick James
– Rick James

2020-10-17 15:48:20 +00:00
Commented Oct 17, 2020 at 15:48 — Rick James
– Rick James, Commented Oct 17, 2020 at 15:48
Thanks. It is typo, I just fixed it.

Chaeun Gong
– Chaeun Gong

2020-10-17 21:53:27 +00:00
Commented Oct 17, 2020 at 21:53 — Chaeun Gong
– Chaeun Gong, Commented Oct 17, 2020 at 21:53

GMB · Accepted Answer · 2020-10-17 15:45:59Z

1

If you have an index defined on (credit limit, salesRepEmployeeNumber), this query can be fulfilled from the index alone without touching the data pages.

edited Oct 17, 2020 at 15:45

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Oct 17, 2020 at 11:49

Gordan Bobić

1,91815 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

GMB Over a year ago

@ChaeunGong: you should create the index that this answers suggests, so your query executes quickly.

Chaeun Gong Over a year ago

@GMB: Thanks your comment. I know that. But I want to know why does the query optimizer use full index scan not full table scan?

Chaeun Gong Over a year ago

I'm sorry. When I checked the post, the content was strange, so I modified the post. My point is why the query optimizer uses 'full index scan' when there are no indexes associated with creditLimits.

Gordan Bobić Over a year ago

Can you provide output of SHOW CREATE TABLE customers; ?

Collectives™ on Stack Overflow

MySQL-Why does this query use full index scan?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related