select with subquery takes about 1min in sqlite and < 1s in SQL Server
I already looked up many posts about this problem (subqueries being very slow in sqlite). but I'm not really good with sql and i don't know what i can do.
i have this query:
SELECT * FROM data d WHERE d.category = 3 AND
(SELECT COUNT(id) FROM data_tag WHERE data = d.id AND (tag = 2136 OR tag = 8)) >= 2
ORDER BY id ASC
i have 3 tables data,tag and data_tag (n-m of data and tag) every data has n tags and in this query i search datas by tags (2 tags, both must be in data)
i switched my database from SQL Server to sqlite and besides this query everything works fine. in SQL Server this one took less then 1 sec to execute and in sqlite it takes about 1m开发者_Python百科in. plz give me some tips.
SELECT d.* FROM data d
INNER JOIN data_tag ON data_tag.data = d.id AND (tag = 2136 OR tag = 8)
WHERE d.category = 3
GROUP BY d.id
HAVING COUNT(data_tag.id) >= 2
ORDER BY id ASC
Try:
SELECT d.*
FROM DATA d
JOIN (SELECT dt.data,
COUNT(id) AS num_tags
FROM DATA_TAG dt
WHERE dt.tag IN (2136, 8)
GROUP BY dt.data
HAVING COUNT(id) >= 2) x ON x.data = d.id
ORDER BY d.id
Don't need ASC
in the ORDER BY - that's the default.
精彩评论