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
acoresponding 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...
Any help would be greatly appreciated. I hope I am naively missing some obvious alternative strategy, since this sort
ofoperation must be common in databases.
Thanks,
Ken
--
Ken Simpson, CEO
MailChannels Corporation
Reliable Email Delivery (tm)
http://www.mailchannels.com