I have a question for the SQLite experts here as to the most efficient way to retrieve the following:
Say we have a table: CREATE TABLE FOO ( PARENT_ID INTEGER, CHILD_ID INTEGER, REVISION INTEGER ); CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION); note that we could use CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC); however, the current documentation implies that DESC does not improve performance in the below. If we have the following data: INSERT INTO FOO VALUES (1,2,1); INSERT INTO FOO VALUES (1,2,2); INSERT INTO FOO VALUES (1,2,3); My goal is two fold. 1. Given a parent_id and a child_id, retrieve the last rowid select max(rowid) from foo where parent_id = :parentId and child_id = :childId 2. Given a parent_id, a child_id, and a revision, retrieve the last rowid before that revision: select max(rowid) from foo where parent_id = :parentId and child_id = :childId and revision < :revision My concern is that the queries above are suboptimal and will become a performance impediment to the application. Can anyone recommend a more efficient method of retrieving the data? John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
