Explain query - MySQL not using index from table

Question

I'm trying to learn the explain statement in MySQL but ran into a wall.

For my experiment, I created two tables (each having 10 rows) and ran explain over a simple join. Naturally, no indexes were used and 10*10 = 100 rows were scanned (I've added the output in images because the very long output of EXPLAIN was being wrapped on itself. The code is also in this pastebin):

Bad table design

I then added primary keys and indexes and reissued the explain command:

enter image description here

But as you can see, the users table is still being fully scanned by MySQL, as if there was no primary key. What is going wrong?

if the whole users table was not scanned it would not have joined all the scanned users to accounts , only one where and one ref clause limiting the accounts. - use more records — Shane
– Shane, Commented Jul 2, 2015 at 11:23

Gordon Linoff · Accepted Answer · 2015-07-02 11:24:49Z

3

This is a bit long for a comment.

Basically, your tables are too small. You cannot get reasonable performance indications on such small data -- the query only needs to load two data pages into memory for the query. A nested loop join requires 100 comparisons. By comparison, loading indexes and doing the binary search is probably about the same amount of effort, if not more.

If you want to get a feel for explain, then use tables with a few tens of thousands of rows.

answered Jul 2, 2015 at 11:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

ankush981 Over a year ago

Thanks! So you mean to say I was on the right track? :D

Rick James · Accepted Answer · 2015-07-02 20:14:49Z

You seem to be asking about EXPLAIN, INDEXing, and optimizing particular SELECTs.

For this:

select  u.name
    from  users as u
    join  accounts as a on u.id = a.user_id
    where  a.amount > 1000;

the optimizer will pick between users and accounts for which table to look at first. Then it will repeatedly reach into the other table.

Since you say a.amount > ... but nothing about u, the optimizer is very likely to pick a first.
If a.amount > 1000 is selective enough (less than, say, 20% of the rows) and there is INDEX(amount), it will use that index. Else it will do a table scan of a.
To reach into u, it needs some index starting with id. Keep in mind that a PRIMARY KEY is an index.

This, and many more basics, are covered in my index cookbook.

See also myxlpain for a discussion of EXPLAIN.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

EXPLAIN FORMAT=JSON SELECT... is also somewhat cryptic, but it does have more details than a regular EXPLAIN.

Hytool · Accepted Answer · 2015-07-02 11:39:22Z

1

well, As your main filter has '>' comparison operator, it does full table scan Because it may or may not return all rows.

as you join the 'accounts' table with 'user_id' column, it shows the 'user_id' index in Possible Keys, but it doesn't use it, because of the FULL TABLE SCAN process.

answered Jul 2, 2015 at 11:39

Hytool

1,3781 gold badge8 silver badges22 bronze badges

2 Comments

ankush981 Over a year ago

I don't think the comparison operator always implies a full table scan. That would bring almost all databases to their knees. And by the way, I tested without the where condition and the result is still the same.

Hytool Over a year ago

@dotslash, yes, it will go with a table scan, if you use this comparison operator, because you don't have index on <amount> column.

Collectives™ on Stack Overflow

Explain query - MySQL not using index from table

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related