> On Sep 12, 2017, at 12:55 PM, Richard Hipp <[email protected]> wrote:
>
> But we deliberately omit common subexpression elimination (CSE). This
> is because our research shows that out of the millions of queries that
> SQLite compiles every second, only a very tiny fraction would actually
> benefit from CSE
OMG, I can see the BuzzFeed headline: "SQLite database engine is secretly
sending your queries back to their server for analytics!" ;-)
Seriously, I see your point. But JSON support may have created a new class of
queries that do benefit, because every reference to a JSON property is through
a function call. So while a traditional query can use the same column name
multiple times without incurring overhead, the JSON equivalent repeats a
json_extract call multiple times, which does have overhead.
Traditional:
SELECT * FROM stuff WHERE length(name) = 5 OR name = 'five';
JSON:
SELECT * FROM stuff WHERE length(json_extract(jsn,'$.name')) = 5 OR
json_extract(jsn,'$.name') = 'five';
If CSE optimization is too expensive to do by default, maybe it could be
enabled by a flag when preparing the statement?
Unfortunately using a WITH clause doesn't seem to help; as I suspected, it's
basically used as a macro that gets expanded. Here's one I tried:
WITH docs AS (SELECT rowid as id, json_extract(jsn,'$.name') AS name
FROM stuff)
SELECT id FROM docs WHERE length(name) <= 5 OR name = 'five';
The EXPLAIN command shows that this query still makes two identical calls to
json_extract per row.
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
