using mysql what will be the fastest way to join tables

Question

i have two tables "User" And "UsersSettings" .

and i want to select User's where the users settings is something what query will be more fast ?

SELECT u.*, us.someSettings FROM User u,UserSettings us 
WHERE u.id = us.user_id 
AND us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue

OR

SELECT u.*, us.someSettings FROM User u
LEFT JOIN UserSettings us ON us.user_id = u.id
WHERE us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue

can you help me please thank you

possible duplicate of [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Marcus Adams, May 16 '14 at 15:21

score 1 · Accepted Answer · answered May 16 '14 at 15:18

The correct query is an inner join:

SELECT u.*, us.someSettings
FROM User u INNER JOIN
     UserSettings us
     ON us.user_id = u.id
WHERE us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue;

You have a filtering condition on UserSettings, so the outer join is turned to an inner join anyway. You should avoid implicit join syntax and put the join conditions explicitly in an on clause. This is for readability and maintainability. Both versions should have the same performance.

using mysql what will be the fastest way to join tables

1 Answers1