is this overkill for an sql statement?

[TheDude05]

Its for MySQL 5.x

SELECT a.name, b.* FROM isu_assignments AS a, isu_uploads AS b
WHERE (a.class_id = (SELECT id FROM isu_classes WHERE section='004' && number='143' && dept_id = (SELECT id FROM isu_dept WHERE abbr = 'TEC')))
AND (b.user_id = (SELECT id FROM isu_users WHERE username='ajwilli') AND b.class_id = a.class_id)

To be honest its not even returning the result set the way I like but I was just wondering if that many subqueries is too much

 

[mikeblas]

Overkill? People write SQL statements that are pages long, so this four-liner is hardly over the top.

I also didn't know you could use "&&" in MySQL wher you mean "AND".

But I think you can do most of those subqueries as joins, which ought to give the optimizer a better crack at getting reasonable execution time. I don't see why you wouldn't rewrite it as:

SELECT a.name, b.* FROM isu_assignments AS a, isu_uploads AS b, isu_users, isu_dept
WHERE a.class_id = isu_classes.id
  AND section='004'
  AND number='143'
  AND dept_id = isu_dept.id
  AND abbr = 'TEC'
  AND b.user_id = isu_users.id
  AND isu_users.username='ajwilli'
  AND b.class_id = a.class_id

 

[ameoba]

While the join is nicer, the original wasn't too bad...

Here's an example of what I see on a daily basis:

   CURSOR getchoicesc (aidm NUMBER) RETURN app_type
   IS
      SELECT sarhead_appl_seqno, stvwapp_desc, stvterm_desc, sarhead_add_date,
             sarhead_appl_comp_ind, sarhead_wsct_code_bookmark
        FROM sarhead, stvterm, stvwapp
       WHERE (
                   (
                          bwskalog.pidm IS NOT NULL
                      AND EXISTS (SELECT 1
                                    FROM sabiden
                                   WHERE sabiden_pidm = bwskalog.pidm
                                     AND sarhead_aidm = sabiden_aidm)
                   )
                OR (    bwskalog.pidm IS NULL
                    AND sarhead_aidm = aidm)
             )
         AND sarhead_appl_comp_ind <> 'Y'
         AND stvterm_code = sarhead_term_code_entry
         AND stvwapp_code = sarhead_wapp_code
         AND (
                   stvwapp_code IN
                   (SELECT sarwatr_wapp_code
                      FROM sarwatr
                     WHERE sarwatr_term_code = stvterm_code
                       AND sarwatr_wapp_code = stvwapp_code
                       AND TO_CHAR (SYSDATE) BETWEEN sarwatr_start_date
                               AND sarwatr_end_date)
                OR (
                          stvwapp_code NOT IN (SELECT sarwatr_wapp_code
                                                 FROM sarwatr
                                                WHERE sarwatr_wapp_code =
                                                                  stvwapp_code)
                      AND stvterm_code IN
                          (SELECT soratrm_term_code
                             FROM soratrm
                            WHERE TO_CHAR (SYSDATE) BETWEEN soratrm_start_date
                                      AND soratrm_end_date)
                   )
             )
       ORDER BY sarhead_appl_seqno;

...and that's just a randomly chosen one. There's literally -thousands- of them, many worse than this. Granted, we also have 5-6000 tables and views.

 

[mikeblas]

Is this a "my SQL is bigger than yours" thread?

 

[TheDude05]

ok well that makes me feel better. I had heard mysql isn't very fast when it comes to sub queries. The query I posted is going to be the smallest out of all the queries I have to write and thats why I asked.

 

[mikeblas]

ok well that makes me feel better. I had heard mysql isn't very fast when it comes to sub queries. The query I posted is going to be the smallest out of all the queries I have to write and thats why I asked.

I don't use My SQL much (obviously!) so I can't give perscriptive advice. In SQL Server, you can dump the execution plan very easily and compare it across different statements, looking for the more efficient plan. I expect there's a way to do that in My SQL, so I'd go find it and compare how correlated subselects are implemented with the way joins are implemented. You'll be doing this anyway as you work on getting the right indexes in place.

I wouldn't be surprised if subselect execution is not so great, since I believe My SQL only implemented subselects in this major release.

Does My SQL support the ANSI join syntax, by the way?

SELECT a.name, b.*
  FROM isu_assignments AS a
  JOIN isu_uploads AS b,
    ON b.class_id = a.class_id
  JOIN isu_dept
    ON isu_dept.id = dept_id
  JOIN isu_users
    ON b.user_id = isu_users.id
  JOIN isu_classes
    ON a.class_id = isu_classes.id 
WHERE 
  AND section='004'
  AND number='143'
  AND abbr = 'TEC'
  AND isu_users.username='ajwilli'

 

[HJB417]

subqueries were introduced in version 4 or 4.1.

There's the explain command in mysql

http://dev.mysql.com/doc/refman/4.1/en/explain.html

similar to mssql's show execution path, but not as detailed I believe