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, May 23 '16 at 11:58
Create a large test dataset and run both querier and see which runs faster. — Shadow, May 23 '16 at 11:59
@TimBiegeleisen I've added those two queries to my question. — Shafizadeh, May 23 '16 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, May 23 '16 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, May 23 '16 at 12:01
I would expect the join to be faster than the second query which uses a correlated subquery. But this would depend on size of data set, indices, etc. Start your analysis by looking at the actual queries. `EXPLAIN` will not do the thinking for you, although it may add some insight. — Tim Biegeleisen, May 23 '16 at 12:03
1. The 2 queries are not the same. The returned field is in different tables. 2. The problem is with explain, that the answer is never black or white. It depends. I always check the type, possible keys, key, and extra columns. Meaning of all these is described in the mysql manual page you linked. This is why testing on an appropriately sized data is important. — Shadow, May 23 '16 at 12:08
But it is apparent that you do not have any indexes and that is definitely a bad thing. — Shadow, May 23 '16 at 12:10

score 1 · Accepted Answer · answered May 31 '16 at 01:38

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.

Understanding the result of EXPLAIN in MySQL

1 Answers1