> On Sep 12, 2017, at 12:55 PM, Richard Hipp <d...@sqlite.org> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users