0
SELECT SQL_CACHE se_media.* 
FROM se_media 
  LEFT JOIN se_albums 
    ON se_albums.album_id=se_media.media_album_id 
  LEFT JOIN se_users 
    ON se_albums.album_user_id=se_users.user_id 
  LEFT JOIN ( SELECT s1.media_id AS tableprimary 
               FROM se_media as s1 
               WHERE  s1.media_title 
               LIKE '%funny%' 
             ) AS tagobjects_1 
    ON se_media.media_id=tagobjects_1.tableprimary 
WHERE   media_date_created <= '1318949954' 
  AND media_search='1' 
ORDER BY media_date_created DESC 
LIMIT 0, 10

500 sec

SELECT se_media.* 
FROM se_media 
  INNER JOIN se_albums 
    ON se_albums.album_id=se_media.media_album_id 
  INNER JOIN se_users 
    ON se_albums.album_user_id=se_users.user_id 
  INNER JOIN ( SELECT s1.media_id AS tableprimary 
               FROM se_media as s1 
               WHERE  s1.media_title 
               LIKE '%funny%' 
             ) AS tagobjects_1 
    ON se_media.media_id=tagobjects_1.tableprimary 
WHERE   media_date_created <= '1318949954' 
  AND media_search='1' 
ORDER BY media_date_created DESC 
LIMIT 0, 10

-5 sec only

WHy?

I thought left join is faster than INNER JOIN.

How query executed ? Which order? Find all rows then join and then where ?

thanks.

ypercubeᵀᴹ
  • 109,746
  • 18
  • 170
  • 231
poker king
  • 51
  • 5
  • Check out explain plan. http://dev.mysql.com/doc/refman/5.0/en/using-explain.html – dmcnelis Oct 18 '11 at 20:58
  • Here's an old thread discussing performance of INNER JOIN vs LEFT JOIN: http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server – Marcus Oct 18 '11 at 20:59

0 Answers0