0

These two mysql queries return the same result set. Either gives me the results I want. But is one preferable over the other?

SELECT links.*, users.user_name 
FROM links 
LEFT JOIN terms 
ON links.link_id = terms.terms_link_id 
LEFT JOIN users 
ON links.link_user = users.user_id 
WHERE terms.terms_tag_id = ?

-

SELECT links.*, users.user_name 
FROM links, users, terms 
WHERE links.link_id = terms.terms_link_id 
AND links.link_user = users.user_id 
AND terms.terms_tag_id = ?
Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
mos fetish
  • 506
  • 2
  • 13
  • To be clear - there are just php strings. If you want to ask about **queries** specifically - avoid anything except of sql. – zerkms Dec 20 '10 at 03:17
  • 2
    btw, since you're using `LEFT JOIN` - these 2 queries might return **different results**. – zerkms Dec 20 '10 at 03:19

2 Answers2

3

Looks like this answers your question: Inner join vs Where

Community
  • 1
  • 1
zsalzbank
  • 9,685
  • 1
  • 26
  • 39
  • True, but a third option, a variation on the first query that moves the where clause element to the left join on clause for terms would possibly eliminate the number of records to examine and be slightly faster... – Tahbaza Dec 20 '10 at 03:22
1

Both are bad

SELECT links.*, users.user_name
FROM links 
INNER JOIN terms 
  ON links.link_id = terms.terms_link_id AND terms.terms_tag_id = $tag_id
INNER JOIN users 
  ON links.link_user = users.user_id;

Only one rule

  • do an execution plan, DESC EXTENDED YOUR_QUERY; to check how optimization can be done on mysql
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • Just read this: "CAUTION You should never place restrictions on the rows to be returned in a join’s ON clause; only join conditions of the form t1.col1 = t2.col2 (where t1 and t2 are table aliases) should be placed here. Any restrictions intended to filter the resultset should be placed in a WHERE clause." from "Beginning MySQL Database Design and Optimization: From Novice to Professional" by Jon Stephens and Chad Russell, published in 2004 by Apress. That would suggest the condition after AND above should be in a WHERE clause as it's filtering the resultset. Why would that be suggested? – mos fetish Dec 20 '10 at 05:53
  • @mos fetish - well, do an `show profiles` on both queries, which perform better ? – ajreal Dec 20 '10 at 06:12