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
