On Tue, Dec 14, 2010 at 05:35:31PM +0000, Simon Slavin scratched on the wall: > > 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. That's true, but I'm not sure you understand what that statement is trying to say within the context of SQLite. > 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. No. It would return two rows and exit, just as if you called sqltie3_step() twice, and then called sqlite3_finalize(). Understand that SQLite's VDBE normally process one row at a time. It doesn't "batch" rows and return them. Each time you call _step(), SQLite will read and processes just enough data to return the next row that meets the conditions of the query. Assuming there are no sorts, this generally means reading just enough rows to find the next row that matches the WHERE clause. In most (all?) cases, all LIMIT does is keep track of the number of returned rows, and when that limit is reached, it will return SQLITE_DONE, rather than SQLITE_ROW (along with fresh data). The application can then call _finalize() or _reset(). But the application can call those anytime it wants, regardless of a LIMIT. In either case, the amount of processing done is the same. And that's what's meant by "it will scan all the rows it would need without the limit." Not the grand total "same number", if the queries where allowed to run until completion, but the same number of rows will be scan if you're three steps into a general query (and then abort it), or three steps into a query with a LIMIT 3. Sorts, of course, mean that a whole lot of data needs to be scanned to return that first row, but again-- the LIMIT doesn't change that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
