I never thought of putting an additional condition on the LEFT JOIN. That
seems to do the trick.
My original query, with the sub-SELECT, does work. Both your technique and
mine generate identical results.
I did an EXPLAIN on each technique, but I don't know enough to interpret it.
Since the "rows" is identical except for the last bit, where mine is 4 and
yours is 2, does that mean yours is roughly more efficient by a 2:1 ratio?
My real query is rather more complicated that the one we originally
discussed, but I don't think that affects the comparison. You'll notice that
I changed one of your ON clauses to a WHERE clause, and that did not change
the EXPLAIN output one iota.
Here are the results of the EXPLAINs:
mysql> explain
-> SELECT prod.prod_num,
-> prod_price.prod_price_del_format,
-> "USD",
-> prod_price.prod_price_disp_price,
-> "YEN",
-> prod_price.prod_price_end_price * @exchange AS yen_end_price
->
-> FROM
-> pub JOIN
-> prod JOIN
-> prod_price LEFT JOIN
-> prod_price AS pp ON (prod.prod_id = pp.prod_id AND
pp.prod_price_end_cu
rr = 'YEN')
->
-> WHERE
-> pub.pub_code IN ('ENER', 'FIT', 'GOVT', 'HEAL', 'ID', 'LIFE',
'MANU
')
-> AND pub.pub_id = prod.pub_id
-> AND prod.prod_id = prod_price.prod_id
-> AND prod.prod_discont = 0
-> AND prod_price.prod_price_end_curr = 'USD'
-> AND pp.prod_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: prod_price
type: ALL
possible_keys: prod_id
key: NULL
key_len: NULL
ref: NULL
rows: 75230
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: prod
type: eq_ref
possible_keys: PRIMARY,pub_id
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod_price.prod_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: pub
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod.pub_id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: pp
type: ref
possible_keys: prod_id
key: prod_id
key_len: 46
ref: giiexpr_db.prod.prod_id
rows: 2
Extra: Using where
==============================================================
mysql> explain
-> SELECT prod.prod_num,
-> prod_price.prod_price_del_format,
-> "USD",
-> prod_price.prod_price_disp_price,
-> "YEN",
-> prod_price.prod_price_end_price * @exchange AS yen_end_price
->
->
-> FROM
-> pub JOIN
-> prod JOIN
-> prod_price
->
-> WHERE
-> pub.pub_code IN ('ENER', 'FIT', 'GOVT', 'HEAL', 'ID', 'LIFE',
'MANU
')
-> AND pub.pub_id = prod.pub_id
-> AND prod.prod_id = prod_price.prod_id
-> AND prod.prod_discont = 0
-> AND prod_price.prod_id NOT IN
-> (SELECT prod_price.prod_id
-> FROM prod_price
-> WHERE prod_price.prod_price_end_curr = 'YEN')\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: prod_price
type: ALL
possible_keys: prod_id
key: NULL
key_len: NULL
ref: NULL
rows: 75230
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: prod
type: eq_ref
possible_keys: PRIMARY,pub_id
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod_price.prod_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: pub
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod.pub_id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: prod_price
type: index_subquery
possible_keys: prod_id
key: prod_id
key_len: 46
ref: func
rows: 4
Extra: Using index; Using where
Thoughts? Explanations?
Thanks.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
