php - 添加where条件时mysql查询不起作用

Question

 SELECT tm.MAGAZINE_ID,`MAGAZINE_NAME`, 
 CASE WHEN tsd.no_of_issues IS NULL THEN 1 ELSE tsd.no_of_issues 
 END AS Subscription_Type,
 sum(tu.units) AS Total_sales, 
 SUM(CASE WHEN customer_currency='USD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_USD, 
 SUM(CASE WHEN customer_currency='CAD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_CAD, 
 SUM(CASE WHEN customer_currency='CNY' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_CNY, 
 SUM(CASE WHEN customer_currency='EUR' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_EUR, 
 SUM(CASE WHEN customer_currency='GBP' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_GBP, 
 SUM(CASE WHEN customer_currency='AUD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_AUD, 
 SUM(CASE WHEN customer_currency='MXN' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_MXN, 
 SUM(CASE WHEN customer_currency='CHF' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_CHF, 
 SUM(CASE WHEN customer_currency='NZD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_NZD, 
 SUM(CASE WHEN customer_currency='DKK' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_DKK, 
 SUM(CASE WHEN customer_currency='NOK' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_NOK, 
 SUM(CASE WHEN customer_currency='JPY' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_JPY, 
 SUM(CASE WHEN customer_currency='SEK' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_SEK, 
 SUM(CASE WHEN customer_currency='SGD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_SGD, 
 SUM(CASE WHEN customer_currency='TWD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_TWD, 
 SUM(CASE WHEN customer_currency='HKD' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_HKD, 
 SUM(CASE WHEN customer_currency='CNY' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_CNY, 
 SUM(CASE WHEN customer_currency='IDR' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_IDR, 
 SUM(CASE WHEN customer_currency='AED' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_AED, 
 SUM(CASE WHEN customer_currency='SAR' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_SAR, 
 SUM(CASE WHEN customer_currency='ILS' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_ILS, 
 SUM(CASE WHEN customer_currency='RUB' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_RUB, 
 SUM(CASE WHEN customer_currency='ZAR' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_ZAR, 
 SUM(CASE WHEN customer_currency='TRY' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_TRY, 
 SUM(CASE WHEN customer_currency='INR' THEN tu.developer_proceeds * tu.units ELSE 0 END) AS Revenue_INR 
 FROM `tbl_itunes_report` tu 
 LEFT JOIN tbl_magazine_subscription_dtl tsd ON tsd.subscription_key = tu.sku_key AND ((tu.begin_date >= tsd.start_date AND tu.begin_date < tsd.end_date) OR (tu.begin_date >= tsd.start_date AND tsd.end_date = '0000-00-00')) 
 LEFT JOIN tbl_magazine_subscription ts ON ts.magazine_subscription_id = tsd.subs_id LEFT JOIN tbl_magazine_issue ti ON ti.PurchaseKey = tu.sku_key AND ti.OS_SELECT = 0 
 LEFT JOIN tbl_magazine tm ON tm.magazine_id = ts.magazine_id OR tm.magazine_id = ti.magazine_id 
 WHERE `product_type_identifier` LIKE 'IA%' 
 AND ( tsd.subscription_key IS NOT NULL OR ti.PurchaseKey IS NOT NULL ) 
 AND tu.report_from = 'ecmedia' AND `units` >0 AND tu.begin_date >= "2013-12-01" 
 AND tu.begin_date <= "2013-12-21" AND tm.publisher_id = 120 
 GROUP BY tm.MAGAZINE_ID,tsd.no_of_issues 
 ORDER BY tm.magazine_name,tsd.no_of_issues

我正在使用此查询来生成报告。当我执行这个查询时没有 tm.publisher_id = 120。它工作正常，但是当我将其添加到查询中时，执行时间会增加。您看到的这个查询有什么问题吗？

Answer 1

除了正确的索引之外，由于您使用的是外连接，您很可能希望移动应用于 LEFT JOIN 右侧表的任何条件 (tm.publisher_id = 120) 从该连接的 WHERE 子句到 ON 子句

...
LEFT JOIN tbl_magazine tm 
  ON (tm.magazine_id = ts.magazine_id OR 
      tm.magazine_id = ti.magazine_id) 
 AND tm.publisher_id = 120
...