Alex Rivera | Logout

Speeding up inner joins between a large table and a small table

Asked 2010-02-13T08:46:26.763
51

This may be a silly question, but it may shed some light on how joins work internally.

Let's say I have a large table L and a small table S (100K rows vs. 100 rows).

Would there be any difference in terms of speed between the following two options?:

OPTION 1:                 OPTION 2:
---------                 ---------
SELECT *                  SELECT *
FROM L INNER JOIN S       FROM S INNER JOIN L
ON L.id = S.id;           ON L.id = S.id;

Notice that the only difference is the order in which the tables are joined.

I realize performance may vary between different SQL languages. If so, how would MySQL compare to Access?

sql query-optimization
Edit
Report

1 Answer

4

I know Oracle's not on your list, but I think that most modern databases will behave that way.

You can see in the following execution plan, that there is no difference between the two statements.

It's a full access to each of the two tables (no index in my case), and then a HASH JOIN. Since you want everything from both tables, both tables need to be read and joined, the sequence does not have an impact.

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |   700 |    42  (12)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   100 |   700 |    42  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| S    |   100 |   300 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| L    |   100K|   390K|    38   (8)| 00:00:01 |
---------------------------------------------------------------------------
answered 2010-02-13T09:10:13.903

Your Answer