Reputation: 5417
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):
I then added primary keys and indexes and reissued the explain
command:
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: 1635
Reputation: 142518
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
.
Upvotes: 2
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
Reputation: 1270873
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