CODEWITHSUNDEEP
mysql
GGio
GGio

Reputation: 7653

Is there any performance difference to put INNER JOINs before LEFT/RIGHT JOINS? MySQL

I tend to always put INNER JOINS before any of my LEFT/RIGHT JOINS. I am not sure if this really has any speed difference.

So would this:

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID
LEFT JOIN Table3 ON Table3.ID = Table1.ID

Execute faster/slower than:

SELECT *
FROM Table1
LEFT JOIN Table3 ON Table3.ID = Table1.ID
INNER JOIN Table2 ON Table1.ID = Table2.ID

I tried to google the difference but could not find one. In my opinion I think that INNER JOIN filters out the rows and then LEFT join has to go through few rows.

Upvotes: 1

Views: 47

Answers (2)

Nanne
Nanne

Reputation: 64429

The mysql optimizer looks at your code, your indexes etc and decides not only what to do first, but which (if any) keys are to be used. This is a complicated process and while you can try to influence it, you are most certainly less good equiped then the code ;D

So concluding: no, it does not matter.

Check out the optimizer part of the manual

Especially this part about join order

A combination of a fixed order in which tables are joined and the corresponding table access methods for each table is called query execution plan (QEP). The goal of the query optimizer is to find an optimal QEP among all such possible plans.

You can check this out for yourself by adding EXPLAIN in front of your query and see what execution plan is performed by your database.

Upvotes: 3

frlan
frlan

Reputation: 7270

There is no difference due to the way of SQL is getting parsed and interprated.

Upvotes: 0

Related Questions