Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Which yields better performance?

    When returning a result that consists of data from multiple tables is it better to put the WHERE criteria under the WHERE statement or in the FROM statement when specifying the table join.

    ex:

    SELECT a,b,c
    FROM table1 INNER JOIN table2 INNER JOIN table3
    WHERE
    b=1 AND c IS NOT NULL

    OR

    SELECT a,b,c
    FROM table1 INNER JOIN table2 AND b=1
    INNER JOIN table3 AND c IS NOT NULL

    Thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    neither will run efficiently. YOu have not stated what columns you are running your inner joins on. that is your first step.

  3. #3
    Join Date
    Jun 2005
    Posts
    79
    Oops lol, mind that mistake

    Is there a difference how MySQL will perform each query? Which method is faster?

    SELECT a,b,c
    FROM table1 INNER JOIN table2 ON y1=y2 INNER JOIN table3 ON y3=y4
    WHERE
    b=1 AND c IS NOT NULL

    OR

    SELECT a,b,c
    FROM table1 INNER JOIN table2 ON y1=y2 AND b=1
    INNER JOIN table3 ON y3=y4 AND c IS NOT NULL

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    I'm going to go out on a limb here and say that in general, I'd assume that having the limitations in the join on clause would be better than all conditions in the where clause. My understanding is that something like your first query will join the tables and then from those joins use the where clause. Whereas the second query will be more selective in the join and will search through less records.

    Hard to come up with an elegant way to try to explain that...sorry. Of course, that is just my assumption. http://dev.mysql.com/doc/refman/4.1/en/query-speed.html goes through the optimization processes.

    Of course, it's all going to depend on the data in your tables, table size, indices, locking, etc. The best way is to run both and compare!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •