The Query Planner should decide in the case of the MIN containing query that the best solution is to traverse TheDate in order and return the first hit. This may entail the creation of the necessary index if it does not exist and so the two plans should be more or less identical.
However, if used in a subquery, the inclusion of the LIMIT may preclude flattening whereas the MIN function version will not preclude flattening. Since the most likely alternative to flattening is a co-routine it probably would not make much of a difference. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Thursday, 12 April, 2018 10:24 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE > >On 12 Apr 2018, at 5:16pm, R Smith <ryansmit...@gmail.com> wrote: > >> SELECT MIN(TheDate) -- get the smallest date >> FROM Orak -- from the table with School-days >> WHERE TheDate >= date('now') -- where the school-day is later or >equal to today. >> ; > >This reflects exactly the right structure for the data. The >following may be a little faster: > > SELECT TheDate > FROM Orak > WHERE TheDate >= date('now') >ORDER BY TheDate > LIMIT 1 > >Both the above queries will perform far faster if there is an index >on the "TheDate" column in Orak. I'm not quite sure how your data is >organised but this may perform another job too if it is a UNIQUE >index. > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
