> SELECT * FROM dictionary WHERE word>='hello' LIMIT 2 > would involve SQLite reading all words from 'hello' to the end of the > dictionary first, and then returning just the first two rows.
I guess you meant SELECT * FROM dictionary WHERE word>='hello' ORDER by word LIMIT 2 with existing index on word. And my belief is that it won't scan all rows in this case. I remember testing that, can't test it right now. Hopefully somebody can give a more definite answer to how SQLite behaves in this case. >> Concerning next or previous record: how do you use LIMIT for that >> anyway? I don't see any decent way except some complicated nested >> queries which would be better performed in the hosting language. > > You can use OFFSET 1 LIMIT 1 to find the next record, for example. Then probably your second requirement is wrong: > I start with three things: > ... > * the value of 'id' of a particular record To use OFFSET ... LIMIT ... you need to know row number, not the id of a particular row. And in this case SQLite indeed must scan all records up to a requested one. Pavel On Tue, Dec 14, 2010 at 12:35 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Dec 2010, at 5:22pm, Pavel Ivanov wrote: > >> I believe your conclusion here is slightly wrong. SQLite reads whole >> table into memory and processes all rows only if it doesn't have index >> and it needs to do in-memory sorting before it will be able to >> understand which 2 rows to return. When SQLite uses index for query it >> doesn't scan all rows. > > I understood that it scanned all rows it would need without the LIMIT clause. > In other words > > SELECT * FROM dictionary WHERE word>='hello' LIMIT 2 > > would involve SQLite reading all words from 'hello' to the end of the > dictionary first, and then returning just the first two rows. > >> Concerning next or previous record: how do you use LIMIT for that >> anyway? I don't see any decent way except some complicated nested >> queries which would be better performed in the hosting language. > > You can use OFFSET 1 LIMIT 1 to find the next record, for example. > > Simon. > _______________________________________________ > 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
