When you run the second query, the optimizer does not know that the data
returned from the view is already sorted into the "correct" order and the
inclusion of the "order by" in the view prevents the query flattener from
optimizing out (re-writing) the query into a single query (eliminating the
view). Without flattening, the view is simply a "black box" that returns some
rows in an arbitrary (completely unsorted) order.
If you remove the "order by" from the view, then the query flattener will be
able to flatten the query and re-write and execute as if you had entered:
SELECT used
FROM proverbs
WHERE CAST(used AS INT) <> 0
ORDER BY used
LIMIT 1
This of course depends on the version of SQLite because the capabilities of
flattening views varied a bit from version to version.
See section 10 of https://sqlite.org/optoverview.html
for a long list of things that affect the query flattening/re-write capability.
Your query with a view is equivalent to
SELECT used FROM (SELECT * FROM proverbs WHERE CAST(used AS INT) <> 0 ORDER BY
used ASC) ORDER BY used LIMIT 1
As you can see from the list, the subquery ORDER BY precludes flattening the
query.
---
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-
>[email protected]] On Behalf Of Cecil Westerhof
>Sent: Saturday, 19 August, 2017 05:37
>To: SQLite mailing list
>Subject: [sqlite] Why the high cost of a double sort
>
>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?
>
>The definition of proverbs:
> CREATE TABLE proverbs (
> proverbID INTEGER PRIMARY KEY AUTOINCREMENT,
> proverb TEXT NOT NULL UNIQUE,
> used TEXT DEFAULT 'notUsed'
> );
>
>To be sure I also executed it in sqlite3 after .timer on. Here the
>first
>one almost always gives:
> Run Time: real 0.000 user 0.000000 sys 0.000000
>and sometimes:
> Run Time: real 0.001 user 0.000000 sys 0.000000
>
>The second one gives mostly:
> Run Time: real 0.006 user 0.004000 sys 0.000000
>but sometimes it get as low as:
> Run Time: real 0.003 user 0.004000 sys 0.000000
>
>Both are using 3.16.2. Would 3.20.0 make a difference? Then I could
>install that one and see the results from that. (The JDBC uses
>3.20.2.)
>
>--
>Cecil Westerhof
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
