On Mon, Feb 4, 2019 at 4:52 PM Simon Slavin <[email protected]> wrote:
> On 4 Feb 2019, at 1:55pm, Gerlando Falauto <[email protected]> > wrote: > > > Or (most likely) my understanding of how data is retrieved is plain > wrong... > > Or your understanding how the current version of SQLite is correct, but a > later version of SQLite will have different optimizations and do things > differently. So at some point you'll update your libraries and suddenly > things won't work any more. So if you depend on sorting, future-proof your > code by asking for sorting. > As I just wrote in replty to Luuk's comment, I'm not questioning the usage of ORDER BY. It should be there. I just would expect it to add ZERO overhead. But again, perhaps I'm making the wrong assumption that using the index data would be "already sorted", perhaps it isn't. I wonder if I'd be allowed to add an ORDER BY in the subquery and if that would make any difference -- I remember reading ORDER BY is only allowed in the outer query (which makes perfect sense). > By the way, here's an example of a SQL engine (not SQLite) not using an > index when you though it would. Suppose you have a short table …just 40 > rows: > > CREATE TABLE MyTable (a INTEGER, b TEXT); > CREATE UNIQUE INDEX MT_a ON MyTable (a); > INSERT <40 rows of data into MyTable> > > SELECT a,b FROM MyTable ORDER BY a; > > The assumed plan would be to use the index to retrieve the row order, then > to look up each retrieved row in the table to retrieve the value for b. > This requires one index walk plus 40 table lookups. > > But the engine knows that 40 table lookups takes a long time. It would be > faster to read the table, then sort it internally. It's a table with only > 40 rows, so sorting it would be fast and take only a little memory. That > saves 40 lookups. > > So even though there's an index, it's not a covering index (it doesn't > contain all the data needed) so it won't be used. > That's understandable and I was expecting that. That's why I populated the test dataset with *muuuch* more data -- to avoid corner cases like this. Thanks again for your patience ;-) Gerlando _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
