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
