mysql - 优化查询，多表选择

Question

我有一个连接四个表的 SQL 脚本。我正在使用许多 AND 运算。有什么解决方案如何优化这个脚本吗？

SELECT s.ACCESS_CODE, a.ACCESS_CODE, MIN(b.ID), b.NAME, a.USER_ID, b.PARENT_ID,b.UPDATE_TIME
FROM b_disk_simple_right s, b_user_access a, b_disk_object b, b_file f
WHERE s.ACCESS_CODE = a.ACCESS_CODE 
  AND a.USER_ID = '".$userID."' 
  AND s.OBJECT_ID=b.ID 
  AND f.ID = b.FILE_ID
  AND b.DELETED_BY=0
  AND f.MODULE_ID = 'disk' 
GROUP BY b.ID

Answer 1

尝试如下内连接:

SELECT        s.ACCESS_CODE, a.ACCESS_CODE AS Expr1, MIN(b.ID) AS Expr2, b.NAME, a.USER_ID, b.PARENT_ID, b.UPDATE_TIME
FROM          b_disk_simple_right AS s INNER JOIN
              b_user_access AS a ON s.ACCESS_CODE = a.ACCESS_CODE CROSS JOIN
              b_disk_object AS b CROSS JOIN
              b_file AS f
WHERE        (a.USER_ID = '".$userID."') AND (s.OBJECT_ID = b.ID) AND (f.ID = b.FILE_ID) AND (b.DELETED_BY = 0) AND (f.MODULE_ID = 'disk')
GROUP BY b.ID