One table with this create SQL: CREATE TABLE Table1([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT)
and a non-uique index on PATIENT_ID Then the following 2 queries, that give the same result: select * from Table1 t1 where t1.entry_id in(select max(t2.entry_id) from Table1 t2 where t1.patient_id = t2.patient_id) order by patient_id asc select * from Table1 where rowid in(select t1.rowid from Table1 t1 where t1.entry_id = (select max(t2.entry_id) from Table1 t2 where t1.patient_id = t2.patient_id)) order by patient_id asc Now the second query is about twice as fast and looking at the query plans it seems that it because the second query uses the primary key and the first doesn't: first query: order from detail --------------------------------------- 0 0 TABLE A3Morb41C_F AS t1 WITH INDEX IDX_A3Morb41C_E_PATIENT_ID ORDER BY 0 0 TABLE A3Morb41C_F AS t2 WITH INDEX IDX_A3Morb41C_E_PATIENT_ID ORDER BY second query: order from detail --------------------------------------- 0 0 TABLE A3Morb41C_F USING PRIMARY KEY 0 0 TABLE A3Morb41C_F AS t1 0 0 TABLE A3Morb41C_F AS t2 WITH INDEX IDX_A3Morb41C_E_PATIENT_ID ORDER BY Is there a way to force the use of the primary key in the first query or is there any other way to tackle this? RBS _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
