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:[email protected]] Im
Auftrag von Hick Gunter
Gesendet: Donnerstag, 03. Jänner 2019 07:48
An: 'SQLite mailing list' <[email protected]>
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:[email protected]] Im
Auftrag von Jonathan Moules
Gesendet: Mittwoch, 02. Jänner 2019 17:44
An: SQLite mailing list <[email protected]>
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
[email protected]
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
[email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
