Note that you may pass different item_id values to the second query, which would then return 0 rows, but quickly. If you need to reference the same parameter in more than one location inside the SQL Statement, use explicit names or numbers
SELECT * FROM item_info JOIN (select count(1) from users WHERE item_id = ?1) USING (item_id) where item_id = ?1; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Donnerstag, 03. Jänner 2019 07:48 An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Optimisation opportunity on subquery? It would be easier to reproduce if you had checked the provided sample code for errors first... .) Error: near ")": syntax error .) Error: cannot join using column item_id - column not present in both tables Your first query specifies a full table scan over the users table (via the covering index) to create an intermediate table of counts per item_id, and then selects only one row. Your second query specifies a partial index scan to create an intermediate table of 1 row with the required item_id, which is then joined. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jonathan Moules Gesendet: Mittwoch, 02. Jänner 2019 17:44 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Optimisation opportunity on subquery? Hi List, The below seems to my very-non-expert mind like there's scope for query-plan optimisation. I have two tables (simplified below): CREATE TABLE users ( item_id TEXT REFERENCES item_info (item_id) NOT NULL COLLATE NOCASE, some_data TEXT, ); CREATE INDEX users__item_id__idx ON users ( item_id ); CREATE TABLE item_info ( item_id TEXT PRIMARY KEY ON CONFLICT IGNORE NOT NULL COLLATE NOCASE, more_data TEXT ); There are about 1 million records in users and 100,000 records in item_info. These queries are all fast, taking about 0.002s: select * from item_info where item_id = ?; select count(1) from users group by item_id; select count(1) from users where item_id = ?; But when I try and join them together, they're much slower at about 0.5s. SELECT * FROM item_info JOIN (select count(1) from users group by item_id) USING (item_id) where item_id = ?; I kind of expected SQLite would figure out that the outer WHERE clause also applies to the subquery given the combination of USING and GROUP BY means it has to apply anyway. If I explicitly include the WHERE inside the subquery, the EXPLAIN QUERY PLAN is identical, but it's back to the expected fast speed (0.002s): SELECT * FROM item_info JOIN (select count(1) from users WHERE item_id = ?) USING (item_id) where item_id = ?; sqlite 3.24.0 Cheers, Jonathan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users