Re: [sqlite] step back (again)
Whoops, you're right my example won't use the index: SQLite version 3.5.6 Enter ".help" for instructions sqlite> CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT); sqlite> CREATE INDEX tracksIndex ON tracks (title DESC, id ASC); sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<:firsttitle OR (title=:firsttitle AND id>:firstrow) ORDER BY title DESC, id ASC; 0|0|TABLE tracks sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) ORDER BY title DESC, id ASC; 0|0|TABLE tracks WITH INDEX tracksIndex -Jeff On 3/15/08, Tomas Lee <[EMAIL PROTECTED]> wrote: > On 2008 March 15 (Sat) 05:21:53pm PDT, Jeff Hamilton <[EMAIL PROTECTED]> > wrote: > > What about something like this: > > > > SELECT title FROM tracks > > WHERE singer='Madonna' > >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > > ORDER BY title DESC, rowid ASC > > LIMIT 5; > > > > Then you only have to remember the single title and rowid of the first > > item in the list. You'd have to add the rowid ASC to your index as > > well, but the index already needs to store the rowid so I don't think > > it would take more space. > > > That's a clever idea. But is SQLite's query optimizer smart enough to > know it can use the index on title for that query? If you re-write it > to be > > > SELECT title FROM tracks > WHERE singer='Madonna' > >AND title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) > > ORDER BY title DESC, rowid ASC > LIMIT 5; > > > then it surely should realize that it can use the index on title. > > ___ > 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
Re: [sqlite] step back (again)
On 2008 March 15 (Sat) 05:21:53pm PDT, Jeff Hamilton <[EMAIL PROTECTED]> wrote: > What about something like this: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > ORDER BY title DESC, rowid ASC > LIMIT 5; > > Then you only have to remember the single title and rowid of the first > item in the list. You'd have to add the rowid ASC to your index as > well, but the index already needs to store the rowid so I don't think > it would take more space. That's a clever idea. But is SQLite's query optimizer smart enough to know it can use the index on title for that query? If you re-write it to be SELECT title FROM tracks WHERE singer='Madonna' AND title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) ORDER BY title DESC, rowid ASC LIMIT 5; then it surely should realize that it can use the index on title. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Jeff Hamilton wrote: > It shouldn't matter, the rowid is guaranteed to but unique since it's > the row's key into the table data b-tree. The ORDER BY in my example > adds sorting based on that value when the titles are the same so you > in effect have a unique sort key that is ordered. Ahh. Makes sense now. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
It shouldn't matter, the rowid is guaranteed to but unique since it's the row's key into the table data b-tree. The ORDER BY in my example adds sorting based on that value when the titles are the same so you in effect have a unique sort key that is ordered. -Jeff On 3/15/08, dcharno <[EMAIL PROTECTED]> wrote: > Jeff Hamilton wrote: > > What about something like this: > > > > SELECT title FROM tracks > > WHERE singer='Madonna' > >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > > ORDER BY title DESC, rowid ASC > > LIMIT 5; > > > > Then you only have to remember the single title and rowid of the first > > item in the list. You'd have to add the rowid ASC to your index as > > well, but the index already needs to store the rowid so I don't think > > it would take more space. > > > But, I think the rowid has no specific ordering to it. > > ___ > 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
Re: [sqlite] step back (again)
Jeff Hamilton wrote: > What about something like this: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > ORDER BY title DESC, rowid ASC > LIMIT 5; > > Then you only have to remember the single title and rowid of the first > item in the list. You'd have to add the rowid ASC to your index as > well, but the index already needs to store the rowid so I don't think > it would take more space. But, I think the rowid has no specific ordering to it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
What about something like this: SELECT title FROM tracks WHERE singer='Madonna' AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) ORDER BY title DESC, rowid ASC LIMIT 5; Then you only have to remember the single title and rowid of the first item in the list. You'd have to add the rowid ASC to your index as well, but the index already needs to store the rowid so I don't think it would take more space. -Jeff On 3/15/08, dcharno <[EMAIL PROTECTED]> wrote: > >> In the next query, dump any rows where (title = > >> last_seen_title) and (rowid != last_seen_rowid). > > > > Up until you hit the last seen rowid, yes. That was my first idea as > > well. > > > Right. Tried it in a quick prototype and it seemed to worked okay. > > > > The big thing to remember is that the rowids aren't going to be > > ordered in any way. Their main useful property, in this context, is > > > I was actually trying to think if there was a way to do that. Have a > column of a view that was just an incrementing sequence. Then its a > simple matter of using this virtual index to search for the next > on-screen window. Really have to take some time to learn about these > custom functions. > > > > > > > > ___ > 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
Re: [sqlite] step back (again)
>> In the next query, dump any rows where (title = >> last_seen_title) and (rowid != last_seen_rowid). > > Up until you hit the last seen rowid, yes. That was my first idea as > well. Right. Tried it in a quick prototype and it seemed to worked okay. > The big thing to remember is that the rowids aren't going to be > ordered in any way. Their main useful property, in this context, is I was actually trying to think if there was a way to do that. Have a column of a view that was just an incrementing sequence. Then its a simple matter of using this virtual index to search for the next on-screen window. Really have to take some time to learn about these custom functions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
On Sat, Mar 15, 2008 at 01:36:49AM -0400, dcharno scratched on the wall: > Jay A. Kreibich wrote: > > You quoted the backward example, but I'm going to use the forward version. > > > > In addition to the "last seen title", remember the RowID for every row > > with the same "last seen title". > > > > For the forward query use "AND title>=:firsttitle" and pitch rows > > that match any of the remembered RowIDs. (Backwards use "<=".) > > > > You'll also need to increase your LIMIT by the number of RowIDs you > > remembered. If you don't end up pitching rows and get a full set of > > data before the LIMIT is hit, you can just call _finalize on the > > statement. > > Thanks. > > It seems remembering the rowids would be a bit cumbersome and > potentially error prone esp when you change from scroll down to scroll > up. I think you might get the same result by remembering the last seen > title and its rowid. The big thing to remember is that the rowids aren't going to be ordered in any way. Their main useful property, in this context, is that they're unique and allow you to re-identify a specific record. > In the next query, dump any rows where (title = > last_seen_title) and (rowid != last_seen_rowid). Up until you hit the last seen rowid, yes. That was my first idea as well. The problem with this is that if that row happens to be deleted between queries, you'll throw out all the titles with the last seen title, both new and old. That may or may not be an issue with your application, but I generally like to design things to be as robust as the database itself, and assume any possible combination of operations upon the data. Of course, that solution is easier and uses a known, set amount of memory. My own solution has issues if the list of like-titles goes across more than one scrolling "window." -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Jay A. Kreibich wrote: > You quoted the backward example, but I'm going to use the forward version. > > In addition to the "last seen title", remember the RowID for every row > with the same "last seen title". > > For the forward query use "AND title>=:firsttitle" and pitch rows > that match any of the remembered RowIDs. (Backwards use "<=".) > > You'll also need to increase your LIMIT by the number of RowIDs you > remembered. If you don't end up pitching rows and get a full set of > data before the LIMIT is hit, you can just call _finalize on the > statement. Thanks. It seems remembering the rowids would be a bit cumbersome and potentially error prone esp when you change from scroll down to scroll up. I think you might get the same result by remembering the last seen title and its rowid. In the next query, dump any rows where (title = last_seen_title) and (rowid != last_seen_rowid). Also considered trying to make a sort of signum user defined function that might be able to mask off the rows right in the select statement. I haven't done a custom function yet though, so I'm not sure if it would work. The other thought was to simulate a bidirectional or random-access cursor by first doing a query for rowids and then doing a query for the specific row when requested. Less than ideal certainly, but was more along the line of what the gui folk want to deal with in the first place. Its yuk all around ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
On Fri, Mar 14, 2008 at 09:08:51PM -0400, dcharno scratched on the wall:
> [EMAIL PROTECTED] wrote:
> > This issue keeps coming up so I did a wiki page.
> > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>
> I'm using the method described in the wiki and it was working pretty
> well until I hit a data set where the sorting column was not unique.
>
> Here is the query from the wiki:
>
> SELECT title FROM tracks
> WHERE singer='Madonna'
> AND title<:firsttitle
> ORDER BY title DESC
> LIMIT 5;
>
> Imagine if several songs have the same title -- this could happen, for
> example, if the ID3 tags are messed up ('untitled', 'untiled') or if the
> user has several versions of the same song. Using this query, we'll end
> up skipping over all the songs with the same title.
>
> Any thoughts on how to handle this?
You quoted the backward example, but I'm going to use the forward version.
In addition to the "last seen title", remember the RowID for every row
with the same "last seen title".
For the forward query use "AND title>=:firsttitle" and pitch rows
that match any of the remembered RowIDs. (Backwards use "<=".)
You'll also need to increase your LIMIT by the number of RowIDs you
remembered. If you don't end up pitching rows and get a full set of
data before the LIMIT is hit, you can just call _finalize on the
statement.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"'People who live in bamboo houses should not throw pandas.' Jesus said that."
- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] step back (again)
[EMAIL PROTECTED] wrote:
> This issue keeps coming up so I did a wiki page.
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I'm using the method described in the wiki and it was working pretty
well until I hit a data set where the sorting column was not unique.
Here is the query from the wiki:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
Imagine if several songs have the same title -- this could happen, for
example, if the ID3 tags are messed up ('untitled', 'untiled') or if the
user has several versions of the same song. Using this query, we'll end
up skipping over all the songs with the same title.
Any thoughts on how to handle this?
___
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
[sqlite] step back (again)
[EMAIL PROTECTED] wrote:
> This issue keeps coming up so I did a wiki page.
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I'm using the method described in the wiki and it was working pretty
well until I hit a data set where the sorting column was not unique.
Here is the query from the wiki:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
Imagine if several songs have the same title -- this could happen, for
example, if the ID3 tags are messed up ('untitled', 'untiled') or if the
user has several versions of the same song. Using this query, we'll end
up skipping over all the songs with the same title.
Any thoughts on how to handle this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back
[EMAIL PROTECTED] wrote: sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? If not, did anyone try implementing it? (I guess that the indexes would need backward pointers in order to do it.) This issue keeps coming up so I did a wiki page. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> Very clear and useful article. Thanks! - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] step back
[EMAIL PROTECTED] wrote: > sqlite3_step() is great for scrolling forward through a result set. > Is there a way to scroll backwards? > If not, did anyone try implementing it? > (I guess that the indexes would need backward pointers in order to do it.) > This issue keeps coming up so I did a wiki page. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] step back
sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? If not, did anyone try implementing it? (I guess that the indexes would need backward pointers in order to do it.) Clive - To unsubscribe, send email to [EMAIL PROTECTED] -
