CODEWITHSUNDEEP
mysqlindexing
ankush981
ankush981

Reputation: 5417

Explain query - MySQL not using index from table

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?

Upvotes: 0

Views: 1637

Answers (3)

Rick James
Rick James

Reputation: 142528

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.

  1. Since you say a.amount > ... but nothing about u, the optimizer is very likely to pick a first.

  2. 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.

  3. 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.

Upvotes: 2

Hytool
Hytool

Reputation: 1368

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 3

Related Questions