Thanks David for the alernative solution, that is 500 times faster or
any times faster depending on the number of rows. I hope this does not
derive attention from my point that the original construction is not
very well delt with.
It was only a theoretical query derived from a more complex one. The
atrribute "size" is in this complex query yet an other sub-query,
counting rows in yet another table. I need to see if the alternative
solution is still faster there.
sqlite-users mailing list
[hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Also of note is that when you make an index on ab (size), your original query, unchanged, becomes about 3 times faster than my modification. I'm not sure on what it looks at to decide if a temporary autoindex is worth it, but in this case it would have been.
When you move back to your more complex version I don't know if an expression index would be practical though, you'll have to play around.
From: sqlite-users [mailto:[hidden email]] On Behalf Of E.Pasma
Sent: Friday, July 07, 2017 1:12 PM
To: SQLite mailing list
Subject: [sqlite] Slow query, with correlated sub-sub-query