JOIN on where data, or put it in the where clause?

Question

I'm trying to join a subset of data from one table with data in another table (example below), and from a performance standpoint, i'm wondering what the best way to do this is, and what is most scalable.

The table I am trying to join looks like this (the other tables are already in the query).

vid             kid         uid
1                34          12
1                34          15
2                22          18
2                34          18
2                22          15

My query is like this

SELECT * FROM similar 
RIGHT OUTER JOIN(SELECT event.stuff,  eventdate.stuffshows
FROM events
JOIN evendate ON events.eid=eventdate.eid
WHERE location= 'Paris'
AND event.date
BETWEEN '2009-08-14'
AND '2009-08-17'
GROUP BY event.date
ORDER BY event.date
LIMIT 0 , 5
) mystuff ON event.bid = similar.id
AND event.date=similar.date

I think I can make it into

SELECT * FROM similar 
RIGHT OUTER JOIN(SELECT event.stuff,  eventdate.stuffshows
FROM events
JOIN evendate ON events.eid=eventdate.eid
WHERE 
user.vid=events.vid AND user.uid=15 AND user.kid=22
location= 'Paris'
AND event.date
BETWEEN '2009-08-14'
AND '2009-08-17'
GROUP BY event.date
ORDER BY event.date
LIMIT 0 , 5
) mystuff ON event.bid = similar.id
AND event.date=similar.date

My concern is that I'm kinda mixing a join with a where. Is it worth it for performance to set a second JOIN for user.vid=events.vid? Or is it better to do a WHERE rather than a join?

Any suggestions on this?

Answer 1

If you're interested in the differences in performance, the easiest way to find out is to preface your SELECT statements with EXPLAIN. You'll see which indices are used and how many rows need to be touched, etc.

Answer 2

If you're just looking for a rule of thumb and the performance is equivalent I use what makes it the most logical for my understanding. I put the fields that define the join between the two tables on the join clause, and the filtering conditions in the where clause. Generally you know you have it right if you write a similar query and the join conditions stay the same and only the where clause changes.