On Sat, Nov 30, 2013 at 1:55 PM, Richard Hipp <d...@sqlite.org> wrote: > > If you have a LIMIT 25, then the TEMP B-TREE never holds more than 25 rows, > specifically the top 25 rows seen so far. But SQLite still has to scan > through the entire results set looking for other rows that belong in the > top 25. > > If you only order by the first column, then apparently there are indices > that can be used to cause the results to come out of the query in the > correct order to begin with, so only the first 25 rows of the result set > need to be examined. That's normally much, much faster than scanning the > entire result setting looking for the 25 best. >
Okay, let's assume this query: select a.val, b.val, c.val, d.val from a join b on a.fk = b.pk join c on b.fk = c.pk join d on c.fk = d.pk order by a.val, b.val, c.val, d.val limit 25 If the index on a.val is UNIQUE then the above query is equivalent to the following: select * from ( select a.val aval, b.val bval, c.val cval, d.val dval from a join b on a.fk = b.pk join c on b.fk = c.pk join d on c.fk = d.pk order by a.val limit 25 ) subquery order by aval, bval, cval, dval If the index on a.val is not UNIQUE then the database will have to act slightly smarter and keep selecting rows from the "a" table (even after the 25th one), as long as the value in their a.val column is equal to that of the 25th row. Then it has only to sort those 25 or more rows, something that will generally be almost instantaenous. I assume that sqlite is not using the strategy I describe above, hence the inefficient query plans. For my application the index on a.val is not unique, so I will use a heuristic: select a.val aval, b.val bval, c.val cval, d.val dval from a join b on a.fk = b.pk join c on b.fk = c.pk join d on c.fk = d.pk order by a.val limit 25 * 10 ) subquery order by aval, bval, cval, dval limit 25 For my data this will almost certainly return the correct result set. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
