From: Martin Gainty (mgaintyhotmail.com)
Date: Tue Feb 03 2009 - 12:02:32 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

ON condition uses the same columnname from both source and target tables

whereas any column expressions can go in the WHERE clause...

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

> From: jschwartzthe-infoshop.com
> To: mysqllists.mysql.com
> Subject: WHERE vs. ON
> Date: Tue, 3 Feb 2009 12:24:52 -0500
>
> 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
>
>
>

_________________________________________________________________
Windows Live™ Hotmail®…more than just e-mail.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]