24 mrt 2018, Wout Mertens:
...
SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"
...
SELECT _1, _2 FROM (
SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
"testing"
WHERE _3 < 50
ORDER BY _3 DESC,"id")
...
24 mrt 2018, Don V Nielsen
...
WITH data AS (
SELECT
"id" AS _1,
"json" AS _2,
json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1
...
Assuming an expression index on data (json_extract(json, '$.foo')),
above queries have identical execution plans.
Moreover, in each case the result of the expression is obtained
exclusively form the index. Nowhere it is reevaluated, I confirmed
that using a test function.
Even when the result of the expression is included in the selection,
like in the last query, it is obtained from the index as used for the
WHERE or ORDER BY part. That is actually quite good of the optimizer!
The queries must be equally fast.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
