Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
In my opinion, this question would be more appropriate at Database Administrators. Voting to migrate. –  Andriy M Sep 26 '12 at 10:10
add comment

migrated from stackoverflow.com Sep 26 '12 at 12:22

This question came from our site for professional and enthusiast programmers.

2 Answers

The execution performance differs because:

  • In the first case, when any inner table gets created, it is created without any index unless we specify a column name in the WHERE condition. So the tables temp_A and temp_B get created with the variable in the WHERE condition being taken as an index key. Though if we give index-id in the WHERE condition, it takes time to create that temp table with the index value.

  • In the second case, the join on the 2 tables which are already created based on the index value does not need those pre-processing which happened in the first query.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.