List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 3 2009 5:24pm
Subject:WHERE vs. ON
View as plain text  
Somebody, I think it was somebody from MySQL, said that you should never put
anything into a WHERE clause that could be put into the ON clause of a JOIN.
My guess is that this helps with the optimization, but it seems
counter-intuitive to me. I've never followed that advice, but I'm starting
to experiment with it. My first, rather simple example didn't seem to
accomplish much:

 

giiexpress.com: mysql>explain

    -> SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc

    -> FROM pub JOIN prod ON pub.pub_id = prod.pub_id

    -> WHERE pub.pub_code = 'dc'

    -> AND prod.prod_discont = 0

    -> AND prod.prod_samp_doc IS NOT NULL

    -> ORDER BY prod.prod_num\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: pub

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 652

        Extra: Using where; Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: prod

         type: ref

possible_keys: pub_id

          key: pub_id

      key_len: 46

          ref: giiexpr_db.pub.pub_id

         rows: 112

        Extra: Using where

2 rows in set (0.25 sec)

 

 

giiexpress.com: mysql>explain

    -> SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc

    -> FROM pub JOIN prod ON pub.pub_id = prod.pub_id

    -> AND pub.pub_code = 'dc'

    -> WHERE prod.prod_discont = 0

    -> AND prod.prod_samp_doc IS NOT NULL

    -> ORDER BY prod.prod_num\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: pub

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 652

        Extra: Using where; Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: prod

         type: ref

possible_keys: pub_id

          key: pub_id

      key_len: 46

          ref: giiexpr_db.pub.pub_id

         rows: 112

        Extra: Using where

2 rows in set (0.06 sec)

 

Are the results the same because my query is too simple?

 

Regards,

 

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

 

 <http://www.the-infoshop.com> www.the-infoshop.com

 <http://www.giiexpress.com> www.giiexpress.com

www.etudes-marche.com

 



Thread
WHERE vs. ONJerry Schwartz3 Feb
  • RE: WHERE vs. ONMartin Gainty3 Feb
    • RE: WHERE vs. ONJerry Schwartz3 Feb
      • Re: WHERE vs. ONRob Wultsch3 Feb
  • Re: WHERE vs. ONPerrin Harkins3 Feb
  • Re: WHERE vs. ONBaron Schwartz4 Feb
    • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONJohn Daisley4 Feb
      • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONClaudio Nanni4 Feb
  • Re: WHERE vs. ONMartijn Tonies4 Feb