Question

I want to find new markets that have been added to a plan table after date 2012-05-17. This select works but performance is slow with 16k records. When I remove the second distinct keyword, performance improves greatly and I get the same results, but I still need this select to be much faster. It takes about 25 seconds. There must be a faster way?

select distinct(market) 
from plan
where date_added >  2012-05-17 
and market not in (select distinct(market) from plan where date_added <  2012-05-17 )

Answer 1

这并不肯定会改善你的绩效,而是采取另一种做法:

select market
  from (select market,
               min(date_added) min_date
          from plan
         group by market) as sub_plan
 where sub_plan.min_date >  2012-05-17 ;

此外,请允许我告诫说,我是甲骨质的gu,因此,请引起一些小的原意差异。

Answer 2

Cant you just leave out this whole line?

and market not in (select distinct(market) from plan where date_added <  2012-05-17

它是多余的,因为你已经明确了这一日期——“附加”是指“2012-05-17”。

友情链接