UNABLE TO REPRODUCE. Your CREATE TABLEs do not work (they contain syntax errors).
The query you complain about taking a long time does not and cannot work because it is attempting to join two tables using a common column name, that column name NOT being contained in one of the tables (that is, the statement is in error and the error is thrown as soon as you try and prepare the statement). --- 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 Jonathan Moules >Sent: Wednesday, 2 January, 2019 09:44 >To: SQLite mailing list >Subject: [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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
