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 ? i've read that using index between larges tables is a bad choice, so i have doubts
