Had a look at this suggestion now and it works and uses the PATIENT_ID, ADDED_DATE index, but it is as slow as my delete with Julianday. It looks Pavel's suggestion is the way to do this. Just will have a look now and see if doing the construction with DATE( instead of Julianday is any faster.
RBS On Fri, Sep 18, 2009 at 10:48 PM, Jon Dixon <j.di...@ieee.org> wrote: > From: > "Bart Smissaert" <bart.smissa...@gmail.com> > Then the SQL I was trying to improve: > > DELETE > FROM > TABLE2 > WHERE > PATIENT_ID NOT IN ( > SELECT > TABLE1.PATIENT_ID > FROM > TABLE1 > WHERE > JULIANDAY(TABLE2.START_DATE, '-14 month') > > JULIANDAY(TABLE1.START_DATE) AND > TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) > > ----- > > To my understanding, SQLite will not use indices on function results. I > wonder if you would have more luck (since START_DATE is yyyy-mm-dd) using > > DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( > SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < > DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = > TABLE2.PATIENT_ID > ) > > I believe this will make use of a joint index on PATIENT_ID and START_DATE, > with the preferred order depending on which is the more restrictive term > (I'd guess best would be and index on (PATIENT_ID, START_DATE)). > > Jon Dixon > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
