Optimizing query, select from many tables

Question

I have a SQL script which joins four tables. I am using many AND operations. Is there any solution how to optimize this script?

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

Do this four tables have any relations (implied by foreign keys or otherwise) between them? — Gideon, Aug 11 '15 at 06:46
Why MIN(b.ID) together with GROUP BY b.ID? That's the opposite of the general group by rule. (If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.) — jarlh, Aug 11 '15 at 06:53
@John, explicit JOIN syntax makes no difference performance-wise. — jarlh, Aug 11 '15 at 06:54
@jarlh when you are writing `FROM a,b` it does cartesian product between the tables... only when it's finished it goes to the `WHERE` to remove lines according to your conditions. meaning, it first build a HUGE table from his 4 tables and only then it does the `WHERE `. When you are using joins, it does it on the way... it will never build a HUGE table if it's not required. In the final result you will see the same thing... but smart use with joins.. especially when you are using the correct join will improve performance in almost any case. — John, Aug 11 '15 at 08:39
@John, have you compared the different execution plans? (I.e. explicit vs. implicit join.) — jarlh, Aug 11 '15 at 14:20

score 0 · Answer 1 · answered Aug 11 '15 at 06:57

Try inner join as below :

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

Gareth · Answer 2 · 2015-08-11T07:07:55.630

0

First, you should find out whether this operation is actually restricting the speed of your application. If it's not, then it's probably not worth worrying about. Finally, without changing the schema or indices you won't squeeze much more out of it (a decent DB system will optimise it for you before running it).

edited Aug 11 '15 at 07:07

answered Aug 11 '15 at 07:02

Gareth

36
3

Optimizing query, select from many tables

2 Answers2