Re: Comparing two slices within one table efficiently

> I have a table with the following simplified form:
> 
> create table t (
>  run_id integer,
>  domain_id integer,
>  mta_id integer,
>  attribute1 integer,
>  attribute2 integer,
>  unique(run_id, domain_id, mta_id)
> );
> 
> The table has about 1 million rows with run_id=1, another 1 million
> rows with run_id=2, and so on.
> 
> I need to efficiently query the differences between "runs" - i.e. For
> each (domain_id, mta_id) tuple in run 1, is there a coresponding
> tuple in run 2 where either attribute1 or attribute2 have changed?
> 
> The only way I have been able to think of doing this so far is an
> o(n^2) search, which even with indexes takes a long time. e.g.
> 
>  select * from t t1 where exists (select 1 from t t2 where
> t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1
> != t1.attribute1 or t2.attribute2 != t1.attribute2)
> 
> This query takes millenia...
> 

first, add a change flag change_tf that is set through a trigger
whether this record different from record in the previous run.  second,
create an index on domain and mta where change_tf, so you're only
indexing changed records.

this would allow you to find your changes very efficiently at the
relatively small cost of adding one lookup and one extra index per
insert.


      
____________________________________________________________________________________
Pinpoint customers who are looking for what you sell. 
http://searchmarketing.yahoo.com/