Hi, On Wed, Apr 16, 2008 at 6:26 AM, Nacho Garcia <[EMAIL PROTECTED]> wrote: > hi, i have this table > > *TABLE friends: > *id_usr INT > id_friend INT > > and i have a query in which i return friends from a given user and data > related to each of them stored in some other tables. > > So i do this: > > SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( ) > - UNIX_TIMESTAMP( C.lastConnection ) < 240, C.status, 'disconnected' ) AS > 'connectionstatus' > FROM friends F, user_connections C, user_personaldata P, user_statusmessages > M > WHERE F.id_usr = 1 > AND C.id_usr = F.id_friend > AND P.id_usr = F.id_friend > AND M.id_usr = F.id_friend > ORDER BY connectionstatus ASC > > but i have seen that if there is no rows matching a friend of the user in > the other tables, mysql ignore index and scan all the table. > > > this is not happening with joins. > > SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( ) > - UNIX_TIMESTAMP( C.lastConnection ) < 240, C.status, 'disconnected' ) AS > 'connectionstatus' > FROM friends F > LEFT JOIN user_connections C ON C.id_usr = F.id_friend > LEFT JOIN user_personaldata P ON P.id_usr = F.id_friend > LEFT JOIN user_statusmessages M ON M.id_usr = F.id_friend > WHERE F.id_usr = 1 > ORDER BY connectionstatus ASC > > why is the first query scanning all the table if a row is missing? , there > is any performance different between those queries ?
Your queries are not equivalent. The first is equivalent to an INNER JOIN, not a LEFT JOIN. -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
