CODEWITHSUNDEEP
mysql
tl y
tl y

Reputation: 5

what's the difference between the following queries. which is the most effective way to execute?

A.

SELECT * 
FROM `photo` 
      INNER JOIN `photo_comment` 
          ON `photo`.id = `photo_comment`.photo_id 
LIMIT 0,2

B.

SELECT * 
FROM (SELECT * FROM `photo_comment`) AS T,`photo` 
WHERE T.photo_id = `photo`.id 
LIMIT 0,2

C.

SELECT * 
FROM `photo_comment`,`photo` 
WHERE `photo`.id=`photo_comment`.photo_id 
LIMIT 0,2

what's the difference between the following queries. which is the most effective way to execute?

Thanks in advance for any help and enlightenment.

Upvotes: 0

Views: 61

Answers (2)

John Woo
John Woo

Reputation: 263943

The first query and the third query are the same. The first query uses ANSI SQL-92 format while the third query (the old one) uses SQL-89 format.

The second query is slower (but not totally slower) because it scans the the photo_comment first and joins it with the other table.

Upvotes: 1

Denis Ermolin
Denis Ermolin

Reputation: 5556

First and third queries are similar. Second is the slowest one because of using temporary table to store result of inner select.

Upvotes: 0

Related Questions