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 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 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
