Select with a subquery takes about 1 minute in sqlite and <1s in SQL Server

Question

I have already looked through many posts about this problem (subqueries were very slow in sqlite). but I am not very good at 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, a tag and data_tag (nm data and tags) all data have n tags and in this query I am looking for data by tags (2 tags, both must be in the data)

I switched my database from SQL Server to sqlite and, besides this, everything works fine. in SQL Server, it took less than 1 second to execute, and in sqlite it took about 1 minute. Plz give me some advice.

Answer 1

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

Answer 2

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 

ASC ORDER BY - .