However, if you want to do that then you want to use a correlated subquery as
it is designed for retrieving correlated data by running a subquery per result
row:
select *,
(select count(*) from users where item_id = item_info.item_id) as count
from item_info
where item_id = ?;
and not a subselect in the FROM clause. Tables in the "FROM" clause are
siblings and exist independently of each other. They are not generators ...
(except in some special cases where they are, such as a table valued function).
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Wednesday, 2 January, 2019 13:56
>To: SQLite mailing list
>Subject: Re: [sqlite] Optimisation opportunity on subquery?
>
>Yup, there was a typo, but you guys know what he meant. He's saying
>there WHERE could be pushed through the join to the subquery.
>
>
>sqlite> explain query plan select * from item_info join (select
>item_id, count(1) from users group by item_id) using (item_id) where
>item_id = ?;
>QUERY PLAN
>|--MATERIALIZE 1
>| `--SCAN TABLE users USING COVERING INDEX user__item_id__idx
>|--SEARCH TABLE item_info USING INDEX sqlite_autoindex_item_info_1
>(item_id=?)
>`--SCAN SUBQUERY 1
>
>sqlite> explain query plan select * from item_info join (select
>item_id, count(1) from users where item_id = ?) using (item_id) where
>item_id = ?;
>QUERY PLAN
>|--MATERIALIZE 1
>| `--SEARCH TABLE users USING COVERING INDEX user__item_id__idx
>(item_id=?)
>|--SEARCH TABLE item_info USING INDEX sqlite_autoindex_item_info_1
>(item_id=?)
>`--SCAN SUBQUERY 1
>
>
>It basically comes down to not doing algebra, and proving you can use
>it for each case can be easy for a human, but error prone or long to
>do for a computer, especially for non-trivial cases. Here the WHERE
>clause is on one field, that field is used in an INNER join to the
>subquery, the sub query isn't used anywhere else in the larger query,
>the field that matches in the sub query is the group by target and
>not an aggregate field, etc. All those had to be true, and probably
>more things I'm not thinking of at the moment. So it becomes a not so
>trivial thing to identify and use safely.
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Wednesday, January 02, 2019 3:34 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Optimisation opportunity on subquery?
>
>On 2 Jan 2019, at 4:44pm, Jonathan Moules <jonathan-
>li...@lightpear.com> wrote:
>
>> SELECT
>> *
>> FROM
>> item_info
>> JOIN (select count(1) from users group by item_id)
>> USING (item_id)
>> where item_id = ?;
>
>You have an ambiguous column name, and I don't think SQLite is doing
>what you think it's doing.
>
>Please change the name of the column users.item_id to something else,
>then try your SELECT again.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
