Thesql join by 2 ways – how it works? – which gives performance difference

explainjoin;MySQL

I had 2 different ways to query the content which showed performance difference when executed. The 1st way is 

EXPLAIN select SOME_COLUMNS
from
( select *
  from A
  where CONDITION
) p
inner join
( select *
  from B
)st
on p.id = st.id;

and the output of this query returned:

"id"   "select_type"   "table"    "type"   "possible_keys"   "key"    "key_len"   "ref"   "rows"   "Extra"

 1       PRIMARY     derived3  ALL       NULL                 NULL    NULL    NULL      25607     "   "
 1        PRIMARY    derived2  ALL        NULL                NULL     NULL    NULL      21037   Using where; Using join buffer
 3        DERIVED             A    ALL          NULL               NULL      NULL    NULL      23202    "   "
 2        DERIVED             B    ref        IDX_A_TYPE_ID   IDX_A_ID  98      "   "        12411  Using where

The other way is

EXPALIN SELECT SOME_COLUMNS
FROM A p, B s
WHERE p.id = s.id
AND p.CONDITION;

The output of this looks like this:

id       select_type       table       type       possible_keys       key     key_len    ref     rows   Extra
 1       SIMPLE           p            ref       PRIMARY,IDX_A_TYPE_ID      IDX_A_TYPE_ID    98    const    12411    Using where
 1      SIMPLE            s           ref             PRIMARY       PRIMARY     4   local_db.p.entity_id    1

Why is there such a difference in the query execution plan between the 2 and number of rows being fetched in each step of query execution is more in the 1st approach? Please explain it.

Best Answer

There is a differences because:

  1. In first query doing the following:

    • create temp table with data from A filtering out by CONDITION
    • create temp table with data from B
    • for each row in temp_A we traverse temp_B. In this case temp_A using FULL SCAN, temp_B access by index

  2. Second query

    • For each row from A which fall under CONDITION we join with table B.

As you can see in second query there are "preloading" data into tables and scan works in different way.

Related Question