OK, it looks the construction with DATE instead of Julianday is a bit faster, so best option here seems to be:
DELETE FROM TABLE1 WHERE ENTRY_ID NOT IN ( SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE DATE(T1.ADDED_DATE, '+15 month') > T2.ADDED_DATE) This will use the index on the single field PATIENT_ID, which I need in any case for other queries. So, I now have a faster, simpler query and also less indexes needed. Thanks again for all the suggestions. RBS On Sat, Sep 19, 2009 at 12:03 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: > 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
