MySQL Forums :: Performance :: LEFT JOIN with CASE WHEN

I am building an export file to be loaded into a sales reporting tool using SELECT . . . INTO OUTFILE. The query joins 4 tables and left joins 1 table. When I added the left join, the query execution time went from 20 seconds to roughly 2 1/2 hours. The left join uses CASE . . . WHEN statements to allow joining each row using only the pks that have non-blank values. Here's the query:

SELECT DATE_FORMAT(sales.date, '%m%d%y'), sales.outlet, LPAD(sales.article,4,'0') as article,
CONCAT('+',LPAD(FLOOR(sales.sales * article.fwholesale * 100),9,'0')) as wholesale2,
CONCAT('+',LPAD(FLOOR(sales.sales * article.fcogs * 100),9,'0')) as cost,
CONCAT('+',RPAD('',9,'0')) as margin,
CONCAT('+',LPAD(sales.sales,9,'0')) as cases,
CONCAT(IF(pricing.nsi - article.fwholesale < 0, '-', '+'),LPAD(FLOOR(ABS(sales.sales * (IFNULL(pricing.nsi,0) - article.fwholesale)) * 100),9,'0')) as adjustments,
CONCAT('+',RPAD('',9,'0')) as upchg,
CONCAT('+',RPAD('',9,'0')) as commission,
CONCAT('+',RPAD('',9,'0')) as tax
INTO OUTFILE '/var/export/tmp/budget_fcast_ts'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '\r\n'
FROM forecast2.forecast as sales
JOIN sdac.master_outlet as mo on mo.outlet = sales.outlet
JOIN sdac.master_article as ma on ma.article = sales.article
JOIN forecast2.location on location.uniloc = concat(mo.location, mo.uniloc)
JOIN forecast2.article on article.sapplnt = location.sapplnt and article.article = sales.article
LEFT JOIN forecast2.pricing ON (
CASE pricing.location WHEN '' THEN pricing.location LIKE '%' ELSE pricing.location = mo.location END AND
CASE pricing.uniloc WHEN '' THEN pricing.uniloc LIKE '%' ELSE pricing.uniloc = mo.uniloc END AND
CASE pricing.busType WHEN '' THEN pricing.busType LIKE '%' ELSE pricing.busType = mo.busType END AND
CASE pricing.busTypeExt WHEN '' THEN pricing.busTypeExt LIKE '%' ELSE pricing.busTypeExt = mo.busTypeExt END AND
CASE pricing.channel WHEN ''THEN pricing.channel LIKE '%' ELSE pricing.channel = mo.channel END AND
CASE pricing.keyAcct WHEN ''THEN pricing.keyAcct LIKE '%' ELSE pricing.keyAcct = mo.keyAcct END AND
CASE pricing.type WHEN '' THEN pricing.type LIKE '%' ELSE pricing.type = ma.type END AND
CASE pricing.class WHEN '' THEN pricing.class LIKE '%' ELSE pricing.class = ma.class END AND
CASE pricing.category WHEN '' THEN pricing.category LIKE '%' ELSE pricing.category = ma.category END AND
CASE pricing.package WHEN '' THEN pricing.package LIKE '%' ELSE pricing.package = ma.package END AND
CASE pricing.parentCo WHEN '' THEN pricing.parentCo LIKE '%' ELSE pricing.parentCo = ma.parentCo END AND
CASE pricing.flavor WHEN '' THEN pricing.flavor LIKE '%' ELSE pricing.flavor = ma.flavor END AND
pricing.date = sales.date
)
WHERE sales.sales IS NOT NULL
AND sales.sales > 0
AND article.forecast = 1
AND article.status = 1
AND sales.outlet NOT LIKE '99%'
AND mo.busType != '4'

Here's the EXPLAIN:

+----+-------------+----------+--------+-------------------------+---------+---------+----------------------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-------------------------+---------+---------+----------------------------------------------------+---------+-------------+
| 1 | SIMPLE | sales | ALL | outlet,article | NULL | NULL | NULL | 3453628 | Using where |
| 1 | SIMPLE | ma | eq_ref | PRIMARY | PRIMARY | 4 | forecast2.sales.article | 1 | Using where |
| 1 | SIMPLE | mo | eq_ref | PRIMARY,busType | PRIMARY | 9 | forecast2.sales.outlet | 1 | Using where |
| 1 | SIMPLE | location | eq_ref | PRIMARY | PRIMARY | 9 | func | 1 | Using where |
| 1 | SIMPLE | article | eq_ref | PRIMARY,article,sapplnt | PRIMARY | 8 | forecast2.sales.article,forecast2.location.sapplnt | 1 | Using where |
| 1 | SIMPLE | pricing | ref | PRIMARY,date | date | 3 | forecast2.sales.date | 1641 | |
+----+-------------+----------+--------+-------------------------+---------+---------+----------------------------------------------------+---------+-------------+
6 rows in set (0.00 sec)

I'm assuming the multiple case . . . when statements are the culprit. Honestly, I was surprised that it even worked in the first place. Is there a better way to selectively join the data I need in the left joined table than using case whens? Maybe some more advanced sub_queries?

A little server info:
Ubuntu 9.04 64Bit
2 X Xeon 5150 2.66GHz
20 GB RAM
Mysql 5.1.38 Community

Thanks to anyone who can help.