CODEWITHSUNDEEP
mysqlsqlquery-performanceexplain
Shafizadeh
Shafizadeh

Reputation: 10340

Understanding the result of EXPLAIN in MySQL

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')

Upvotes: 5

Views: 1298

Answers (1)

Rick James
Rick James

Reputation: 142298

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.

Upvotes: 1

Related Questions