I tried that and it picked up the index, but the query was slower plus adding the compound index took some time as well, so the other way is better.
RBS On Sat, Sep 19, 2009 at 6:12 AM, Jon Dixon <j.di...@ieee.org> wrote: > Out of curiosity, would it work any faster to switch the date clause to be > T1.ADDED_DATE > DATE(T2.ADDED_DATE,'-15 month') > and used an index (PATIENT_ID,ADDED_DATE)? > > Jon > > You said: > > 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) > > ________________________________ > From: Bart Smissaert <bart.smissa...@gmail.com> > To: Jon Dixon <j.di...@ieee.org> > Sent: Friday, September 18, 2009 6:38:34 PM > Subject: Re: [sqlite] Force the use of a specified index? > > Had 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
