From:
"Bart Smissaert" <[email protected]>
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
