I don't know if it'd be an interesting optimization. Who's to say what the order ends up as prior to the sort? Take for example if I have a list of dollars and cents being returned from a query. I want the 5 top highest cost items out of 6 possibilities, if I keep the top 5 unsorted, item 6 could have been the very top of the list. Thereby making the desired result wrong.
AFAIK, the Limit command is, without question, the final decision on what is being sent to the caller... or parent-query... or.. output... or... whatever is selected... etc, etc. Formatting of the data output via the CLI is done within the CLI, not the result set. (IE: Pipe, tab, or space delimiters) Indexes also aren't used for sorting, only for finding relevant data. True that if you include an index against a field and return results on that without an ORDER BY, you MIGHT get back a sorted list, but that is due to the fact that the index could be sorted, but you can't guarantee that. ORDER BY would probably end up being near the end of the processing steps. (I'm still going through the 4meg worth of C code to see what its about) The direct answer to the question is: SQLite does the reordering, then only returns the first X number of rows. On Sun, Apr 7, 2013 at 9:17 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 7 Apr 2013, at 2:08pm, Baruch Burstein <bmburst...@gmail.com> wrote: > > > If I issue a select statement with a ORDER BY clause and a LIMIT clause, > > does SQLite do a full sort (assuming no index) and then return the first > X > > rows, or just a partial sort to get the first X sorted results? > > The ORDER BY clause does not know about the LIMIT clause. So even with a > LIMIT 1 it doesn't know that all it needs to do is pick the lowest value > and not bother sorting the rest. > > A limit clause just makes _step() return "I'm run out of result rows" > sooner than normal. It's like doing > > _prepare() > _step() > _step() > _finalize() > > stopping _step() after a few times, before you run out of results. So the > LIMIT clause has no influence on any ORDER BY clause. > > Actually what you suggest is an interesting optimization. But I think it > would introduce too much extra code to be appropriate for SQLite. > > 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
