2017-08-19 20:37 GMT+02:00 Clemens Ladisch <clem...@ladisch.de>:
> Cecil Westerhof wrote:
> > I have the following query:
> > SELECT used
> > FROM usedProverbs
> > LIMIT 1
> >
> > The view useProverbs is defined as:
> > CREATE VIEW usedProverbs AS
> > SELECT *
> > FROM proverbs
> > WHERE CAST(used AS INT) <> 0
> > ORDER BY used ASC
> >
> > But I am told this is not clear and that I should use:
> > SELECT used
> > FROM usedProverbs
> > ORDER BY used
> > LIMIT 1
> >
> > But when I use those in DBBrowser, the first takes almost always 0 ms,
> > while the second takes between 13 and 16 ms. Why does the second one take
> > so much more time?
>
> Because only the first sort can be optimized away with an index
> (which you did not mention).
>
I see that there is an index on used yes:
CREATE INDEX `proverbs_used_idx` ON `proverbs` (`used` )
That explains it. It has been some time I defined the table and forgot it.
> If you believe what you're told (that ORDER BY must be in the
> outermost query), then remove the ORDER BY from the view.
>
I did and that worked.
I was also told that you never should put a sort on a view. Is that true,
or a bit to strong?
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
