Re: SUBQUERY PROBLEM
Sorry I don't have line command so I run it in MySQL Query Brower and I saved it in CSV format.
Query EXPLAIN SELECT DISTINCT ReportID, ReportName, FromDate, ToDate, Market, Entity, Submarket, CurrencyCode, Customer FROM nm1_customer_exempt WHERE FromDate >= '2005-12-01 00:00:00' and ToDate <= '2005-12-31 00:00:00' and CONCAT(Market,Entity,Submarket,CurrencyCode,Customer) NOT IN (SELECT DISTINCT CONCAT(nce.Market,nce.Entity,nce.Submarket,nce.CurrencyCode,nce.Customer) FROM nm1_customer_exempt nce WHERE nce.FromDate >= '2005-11-01 00:00:00' and nce.ToDate <= '2005-11-30 00:00:00'), Mon Feb 13 11:57:19 2006
result:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'nm1_customer_exempt', 'ALL', 'IDX_ToDate,IDX_FromDateToDate,IDX_FromDate', '', '', '', 27010, 'Using where; Using temporary'
2, 'DEPENDENT SUBQUERY', 'nce', 'range', 'IDX_ToDate,IDX_FromDateToDate,IDX_FromDate', 'IDX_ToDate', '9', '', 13267, 'Using where; Using temporary'
Query:
EXPLAIN SELECT
e1.ReportID
, e1.ReportName
, e1.FromDate
, e1.ToDate
, e1.Market
, e1.Entity
, e1.Submarket
, e1.CurrencyCode
, e1.Customer
FROM nm1_customer_exempt e1
LEFT JOIN nm1_customer_exempt e2
ON e1.Market = e2.Market
AND e1.Entity = e2.Entity
AND e1.Submarket = e2.Submarket
AND e1.CurrencyCode = e2.CurrencyCode
AND e1.Customer = e2.Customer
AND e2.FromDate >= '2005-11-01 00:00:00'
AND e2.ToDate <= '2005-11-30 00:00:00'
WHERE e1.FromDate >= '2005-12-01 00:00:00'
AND e1.ToDate <= '2005-12-31 00:00:00'
AND e2.ReportID IS NULL
Result:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'e1', 'ALL', 'IDX_ToDate,IDX_FromDateToDate,IDX_FromDate', '', '', '', 27010, 'Using where'
1, 'SIMPLE', 'e2', 'ref', 'IDX_ToDate,IDX_FromDateToDate,IDX_SubmarketCode,IDX_ALL,IDX_FromDate', 'IDX_ALL', '69', 'consolidatetaxe.e1.Market,consolidatetaxe.e1.Entity,consolidatetaxe.e1.Submarket,consolidatetaxe.e1.CurrencyCode,consolidatetaxe.e1.Customer', 2, 'Using where; Not exists'