Re: [sqlite] Selecting last item in a unique index
Dennis Cote wrote: > John Elrick wrote: > >> Last rowid. Rowid will be a key linking to other tables. >> >> > > What table will rowid be linked to? Are you linking children rows back > to parent rows? > > Not exactly. This is a theoretical implementation of WinFS modified to our specific needs. There will be multiple tables, each representing a persistent class, with this table and one other serving as common ground for an interntal hierarchical representation. > You should generally use an excplict column as a foreign key (i.e. for > linking) to other tables. You can use SQLite's "integer primary key" > optimization which stores a column declared that way as the table's rowid. > > Very true. The final form would contain a separate id field. >> Because they must locate the MAX value which to my mind requires more >> "work" to be done that a simple: >> >> select rowid from foo where parent_id = :parentId and child_id = :childId >> >> and then stepping the first row and discarding the rest...but I could be >> wrong which is why I was asking >> >> > > Well if you don't know the max value before hand, then you have to > search the table to find it before you can use it to get any other > results. Always assume your table is scrambled to an arbitrary row order > before each query since SQL works with sets of rows, not ordered tables > like a spreadsheet. > Given the usage, normally the inserts would always be ascending (by normally, I cannot think of a case where that would not be true right now, but I'm not far enough into the design phase to rule it out yet) and that "may" mean there is an optimization we could use. However, I wanted to check before trying to cheat. Thanks, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting last item in a unique index
John Elrick wrote: > > Last rowid. Rowid will be a key linking to other tables. > What table will rowid be linked to? Are you linking children rows back to parent rows? You should generally use an excplict column as a foreign key (i.e. for linking) to other tables. You can use SQLite's "integer primary key" optimization which stores a column declared that way as the table's rowid. > > Because they must locate the MAX value which to my mind requires more > "work" to be done that a simple: > > select rowid from foo where parent_id = :parentId and child_id = :childId > > and then stepping the first row and discarding the rest...but I could be > wrong which is why I was asking > Well if you don't know the max value before hand, then you have to search the table to find it before you can use it to get any other results. Always assume your table is scrambled to an arbitrary row order before each query since SQL works with sets of rows, not ordered tables like a spreadsheet. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting last item in a unique index
Ken wrote: > select * from foo > where rowid = > (select rowid from foo > where parent_id = 1 > and child_id = 2 > group by parent_id, child_id > having revision = max(revision)) > > sqlite> explain query plan >...> select * from foo >...> where rowid = >...> (select rowid from foo >...> where parent_id = 1 >...> and child_id = 2 >...> group by parent_id, child_id >...> having revision = max(revision)) >...> ; > order|from|detail > 0|0|TABLE foo USING PRIMARY KEY > 0|0|TABLE foo WITH INDEX FOO_IDX1 ORDER BY > > > Would the above be faster due to the use of the PRIMARY key access ? > Ken, I'm not sure about the general case, but in this case I suspect not because of SQLite's index data optimization. If all the required columns can be returned from an index, then SQLite will pull the data from the index and never actually reference the base table. That condition is true here since all the column are in the index. I have copied the explain output for both queries below. My query looks more direct and uses the index optimization to return the data. I would have to measure the actual execution time to be sure, but I suspect it will execute faster as well. C:\Documents and Settings\DennisC>sqlite3 SQLite version 3.5.6 Enter ".help" for instructions sqlite> sqlite> CREATE TABLE if not exists FOO ( ...>PARENT_ID INTEGER, ...>CHILD_ID INTEGER, ...>REVISION INTEGER ...> ); sqlite> sqlite> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION); sqlite> sqlite> .explain sqlite> explain ...> select * from foo ...> where parent_id = :parentId ...> and child_id = :childId ...> and revision = ...> (select max(revision) from foo ...> where parent_id = :parentId ...> and child_id = :childId) ...> ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parent_id = :parentId and child_id = :childId) ; 00 1 Goto 0 48000 2 OpenRead 2 3 0 keyinfo(3,BINARY,BINARY) 00 3 SetNumColumns 2 4 000 4 Variable 1 2 0 :parentId 00 5 IsNull 2 46000 6 Variable 2 3 0 :childId 00 7 IsNull 3 46000 8 If 7 36000 9 Integer1 7 000 10Null 0 8 000 11Integer1 9 000 12MustBeInt 9 0 000 13IfZero 9 36000 14Null 0 11000 15Null 0 10000 16OpenRead 3 3 0 keyinfo(3,BINARY,BINARY) 00 17SetNumColumns 3 4 000 18Variable 1 130 :parentId 00 19IsNull 1330000 20Variable 2 140 :childId 00 21IsNull 1430000 22MakeRecord 132 12dddb 00 23MoveLe 3 3012 00 24IdxLT 3 3012 00 25Column 3 2 17 00 26CollSeq0 0 0 collseq(BINARY) 00 27AggStep0 1710max(1) 01 28Goto 0 30000 29Prev 3 24000 30Close 3 0 000 31AggFinal 101 0 max(1) 00 32SCopy 1017000 33Move 178 000 34AddImm 9 -1000 35IfZero 9 36000 36SCopy 8 4 000 37IsNull 4 46000 38MakeRecord 2 3 1 dddb 00 39MoveGe 2 46100 40IdxGE 2 46101 41Column 2 0 18 00 42Column 2 1 19 00 43Column 2 2 20 00 44ResultRow 183 000 45Next 2 40000 46Close 2 0 000 47Halt 0 0 000 48
Re: [sqlite] Selecting last item in a unique index
Dennis Cote wrote: > John Elrick wrote: > >> I have a question for the SQLite experts here as to the most efficient >> way to retrieve the following: >> >> SNIP > John, > > Do you mean the last rowid, or do you really mean the last revision? > > Last rowid. Rowid will be a key linking to other tables. SNIP > > >> 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? >> >> > > What makes you think these queries are sub-optimal? They will both use > the index to find the correct revision number, and then use the index to > find the matching row. Because they must locate the MAX value which to my mind requires more "work" to be done that a simple: select rowid from foo where parent_id = :parentId and child_id = :childId and then stepping the first row and discarding the rest...but I could be wrong which is why I was asking John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting last item in a unique index
select * from foo where rowid = (select rowid from foo where parent_id = 1 and child_id = 2 group by parent_id, child_id having revision = max(revision)) sqlite> explain query plan ...> select * from foo ...> where rowid = ...> (select rowid from foo ...> where parent_id = 1 ...> and child_id = 2 ...> group by parent_id, child_id ...> having revision = max(revision)) ...> ; order|from|detail 0|0|TABLE foo USING PRIMARY KEY 0|0|TABLE foo WITH INDEX FOO_IDX1 ORDER BY Would the above be faster due to the use of the PRIMARY key access ? Regards, Ken Dennis Cote <[EMAIL PROTECTED]> wrote: John Elrick wrote: > 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 > John, Do you mean the last rowid, or do you really mean the last revision? If you mean the latter then this should do it. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :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 > Similarly, for the second case. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :parentId and child_id = :childId and revision < :revision) You need to be careful not to assume any particular ordering of the rows in the table. > 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? > What makes you think these queries are sub-optimal? They will both use the index to find the correct revision number, and then use the index to find the matching row. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Dennis Cote <[EMAIL PROTECTED]> wrote: John Elrick wrote: > 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 > John, Do you mean the last rowid, or do you really mean the last revision? If you mean the latter then this should do it. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :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 > Similarly, for the second case. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :parentId and child_id = :childId and revision < :revision) You need to be careful not to assume any particular ordering of the rows in the table. > 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? > What makes you think these queries are sub-optimal? They will both use the index to find the correct revision number, and then use the
Re: [sqlite] Selecting last item in a unique index
John Elrick wrote: > 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 > John, Do you mean the last rowid, or do you really mean the last revision? If you mean the latter then this should do it. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :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 > Similarly, for the second case. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :parentId and child_id = :childId and revision < :revision) You need to be careful not to assume any particular ordering of the rows in the table. > 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? > What makes you think these queries are sub-optimal? They will both use the index to find the correct revision number, and then use the index to find the matching row. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting last item in a unique index
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
