On Tue, Dec 14, 2010 at 06:31:28PM +0000, Simon Slavin scratched on the wall:
>
> On 14 Dec 2010, at 6:24pm, Jay A. Kreibich wrote:
>
> > 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.
>
> So what you appear to be saying is that if there's an index which is
> suitable to the 'WHERE' and 'ORDER BY' clauses, then SQLite will
> handle the situation correctly: looking at just those records it
> needs to, and quitting as soon as the LIMIT number is reached. It's
> only if there's no useful index that the engine has to scan a lot
> of the table.
Yes, but again... that's true of the same query without the LIMIT.
In short, "LIMIT" should never add overhead. The cost should always
be the same are returning the same number of rows from a "standard"
query, and then resetting the query before it runs to completion.
-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
