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
