non-returned column aliases for repeating expressions?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

non-returned column aliases for repeating expressions?

wmertens
Hi list,

I often have (autogenerated) queries like

SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"

where the json_extract(json, '$.foo') is indexed

I wonder if it would be more efficient to write instead

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")

, so aliasing the common expression? Or is that just extra work for SQLite?

If I did this, I would want to do it always, also when I'm using the
expression only once (so only ordering or only filtering)

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: non-returned column aliases for repeating expressions?

Don V Nielsen
I'm not the sharpest tool in the shed, here, but a couple of observations /
ideas. First, why in the ORDER clause use _3 and then "id" instead of _1?
Using one alias and not the other is inconsistent and could be confusing.

Personally, I would have did the following. Acquire the data using the CTE.
The select what is needed and order it from the CTE. Being script
generated, I find it really easy to create the primary select as a template
and insert the select within the CTE.

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


I read a lot about efficiency with sqlite here. I guess I've become
complacent about it. I find sqlite so fast that efficiency is distracting.
Just me.

dvn

On Sat, Mar 24, 2018 at 11:31 AM, Wout Mertens <[hidden email]>
wrote:

> Hi list,
>
> I often have (autogenerated) queries like
>
> SELECT "id" AS _1,"json" AS _2 FROM "testing"
> WHERE json_extract(json, '$.foo') < 50
> ORDER BY json_extract(json, '$.foo') DESC,"id"
>
> where the json_extract(json, '$.foo') is indexed
>
> I wonder if it would be more efficient to write instead
>
> 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")
>
> , so aliasing the common expression? Or is that just extra work for SQLite?
>
> If I did this, I would want to do it always, also when I'm using the
> expression only once (so only ordering or only filtering)
>
> Wout.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: non-returned column aliases for repeating expressions?

E.Pasma
In reply to this post by wmertens
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users