Re: Why is this query slow?

From: Mladen Gogala <mgogala_at_no.address.invalid>
Date: Fri, 12 Mar 2010 14:12:46 +0000 (UTC)
Message-ID: <hndi4u$nl$5_at_solani.org>

On Fri, 12 Mar 2010 02:34:02 -0800, vsevolod afanassiev wrote:

> select * from transactions
> where tx_date between (date1 and date2) and status = 'AAA' and
> ID not in
> (select ID from transaction where tx_date between (date3 and date4) and
> status = 'AAA')

The problem is probably in the subquery. The "ID not in" part is resolved by executing the subquery and checking that none of the retrieved results matches the required ID in a gigantic loop. You could try something like this:

select * from transactions tx1
where tx1.tx_date between (date1 and date2) and status = 'AAA' and
not exists
(select 1 from transaction tx2 where tx2.tx_date between (date3 and date4) and tx2.status = 'AAA' and tx2.id = tx1.id )

Logically, that is the same thing. If ID is the primary key column, there will be a unique key lookup and you're done.

-- 
http://mgogala.byethost5.com

Received on Fri Mar 12 2010 - 08:12:46 CST