On 2015-03-03 02:43 PM, Richard Hipp wrote: > On 3/3/15, Jan Asselman <jan.asselman at iba-benelux.com> wrote: >> Most of my queries are in the form >> "SELECT * FROM test WHERE a == ? AND b < ?;" >> and use the primary key index so that the rows are returned in the expected >> order without using the ORDER BY statement. > Do not rely on this behavior! It might change at any moment! > > If you omit the ORDER BY clause, the database engine is free to return > rows in any order it chooses. SQLite sometimes uses this freedom to > choose non-intuitive query plans that run faster. It might use this > freedom even more in the future, thus breaking your application if you > omit the ORDER BY clause. >
That is of course very important, but also something else - Please do not try to "Help" the query planner with all manner of omissions or query semantics or relying on PK orders etc, in stead, ask exactly what you want of it, and specify the order. Mostly, the query planner will find the fastest possible way, and if you ask it a good question and it takes long to compute, please notify us on here because chances are we all would like that performance issue fixed.
