>On 2016/10/05 5:27 PM, Andrii Motsok wrote: >> Date: Mon, 3 Oct 2016 16:25:09 +0200 >> >> >> Hi, >> >> 1) In case of ORDER BY if we choose ordering which does not follow "index >> ordering" we will get performance degradation. >> 2) If we use ORDER BY can we be sure that order of rows is the same for two >> subsequent calls (without any writes to database) for non UNIQUE index? >> 3) Just from curiosity - could you please provide any real world scenario >> which shows how two similar subsequent calls can return different order of >> rows? >> >> Regards, >> Andrii
>1) - How do you mean "follow index ordering"? If you order (Ascending or >descending) on fields that are contained in an index, you will get full >performance. > >2) No you can't be sure, well, currently that should be the case because >the QP and sort orderer won't change algorithms between two sorts, but >this is a very unsafe assumption. Any DB update or insert or vacuum or >analyze might alter the row order produced where the ordering isn't >explicit. > >3) We can show you what /might/ produce different row orders, but I >don't know how to show what will definitely guarantee a different order. >For the same reason it is unsafe to "assume" a static ordering, I can >also not "assume" a different ordering unless specifically asking the >sorter to adhere to another ordering. Point is, if you depend on the >order, you have to specify it precisely. You can always order by more >than 1 column, or even a function on a column. Why not simply Order by >your non-unique field AND then by row_id. This will guarantee the same >order. >SELECT * FROM t ORDER BY t.NonUniqueField, t.row_id >(That is, unless you are using WITHOUT ROWID tables, in which case use >the PK.) >>> How do you mean "follow index ordering"? This is only my observation that sqlite usually returns rows in the order in which they are sorted in by index. So if I have two indexes and SELECT with WHERE followed by AND choice of indexes can be different. This choice depends on query planner and statistics. So there is always a chance that ORDER BY will require additional sorting. >>>Any DB update or insert or vacuum or analyze might alter the row order >>>produced where the ordering isn't explicit. I am interested only in the same ordering between two subsequent query executions. The things which can happen in between are: * checkpointing * close/open in with different journal mode: DELETE->WAL/WAL->DELETE >>>Why not simply Order by your non-unique field AND then by row_id Unfortunately this is not simple. We don't have row_id. I am the library on top of sqlite which accepts and executes SQL queries. I cannot force my users to add ORDER BY to all queries especially for non UNIQUE indexes. And I need to do some work under cover (checkpoint and reopen in DELETE mode) and then I need to restore queries statues to their previous position. Regards, Andrii _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
