Understanding the result of EXPLAIN in MySQL

Question

I have two separated queries which have identical outputs. Now I'm trying to understand which one is better?

Query1:

| id | select_type | table | type | possible_keys |    key | key_len |    ref | rows |                                              Extra |
|----|-------------|-------|------|---------------|--------|---------|--------|------|----------------------------------------------------|
|  1 |      SIMPLE |    t1 |  ALL |        (null) | (null) |  (null) | (null) |    9 |                                        Using where |
|  1 |      SIMPLE |    t2 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where; Using join buffer (Block Nested Loop) |

Query2:

| id |        select_type | table | type | possible_keys |    key | key_len |    ref | rows |       Extra |
|----|--------------------|-------|------|---------------|--------|---------|--------|------|-------------|
|  1 |            PRIMARY |    t1 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where |
|  2 | DEPENDENT SUBQUERY |    t2 |  ALL |        (null) | (null) |  (null) | (null) |    9 | Using where |

So which one is better and why?

I read about EXPLAIN here, But still I don't know which parameter is important? Or which parameter shows me such a column needs to be index, or my query needs to be optimized?

In those two explain's results above, all columns are identical except: select_type and extra. So which one is better:

- SIMPLE, SIMPLE
- PRIMARY, DEPENDENT SUBQUERY
- Using where, Using where; Using join buffer (Block Nested Loop)
- Using where, Using where

EDIT: Here is those two queries:

Query1:

SELECT t2.color FROM mytable t1
                JOIN mytable t2 ON t1.related = t2.id
                WHERE t1.id = '4'

Query2:

SELECT t1.color FROM mytable t1
    WHERE exists (select 1 from mytable t2
             where t1.id =  t2.related
               and t2.id ='4')

Could you show us the two queries, assuming they would reasonably fit in the question? And what is the current running time of your two queries? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 23, 2016 at 11:58
Create a large test dataset and run both querier and see which runs faster. — Shadow
– Shadow, Commented May 23, 2016 at 11:59
@TimBiegeleisen I've added those two queries to my question. — Shafizadeh
– Shafizadeh, Commented May 23, 2016 at 12:01
"Or which parameter shows me such a column needs to be index, or my query needs to be optimized?" There are no such columns in the explain. It would be too easy if explain told us how to change your query. It is up to you to figure this out, and explain is a good tool to help in this. — Shadow
– Shadow, Commented May 23, 2016 at 12:01
@Shadow Yes a benchmark would be helpful. But actually I'm trying to understand how EXPLAIN helps me in such a cases. — Shafizadeh
– Shafizadeh, Commented May 23, 2016 at 12:01

Rick James · Accepted Answer · 2016-05-31 01:38:48Z

1

The important thing this time is possible keys: NULL. That is, you have no indexes. Since the table has only about 9 rows, this is not a performance problem. Yet. That query will hit about 9*9 = 81 rows. If your table gets to 1000 rows, that will be 1000000 rows hit to return the resultset.

Step 1 in using a database is to learn about keys (indexes).

With the appropriate index(es), this query should touch about 2 rows, regardless of the size of the table.

You probably need PRIMARY KEY(id). It would helps us if you provided SHOW CREATE TABLE mytable.

A quick lesson on building indexes

Learning about EXPLAIN requires a foundation in indexing. It is premature to discuss what EXPLAIN is saying.

answered May 31, 2016 at 1:38

Rick James

144k15 gold badges144 silver badges254 bronze badges

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

Collectives™ on Stack Overflow

Understanding the result of EXPLAIN in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related