Geek asks and answers

Slow MySQL IN query - how to convert to JOINs?

Currently, my current MySQL query takes up to 10 seconds to run in my application:

SELECT tagid, tag FROM tags WHERE tagid IN 
(SELECT DISTINCT tagid FROM news_tags WHERE newsid IN 
(SELECT newsid FROM news_tags WHERE tagid IN (16,32)
GROUP BY newsid HAVING COUNT(newsid)>=2)) 
AND tagid NOT IN (16,32) ORDER BY level, tagid

Used tables:

  • table news_tags, with columns newsid,tagid
  • a table tagswith columns tagid, tag,level

The purpose of the query is to find news items tagged with tags tagid16 and 32, and then find other tags for which news tags have also been tagged, in order to provide the user with the opportunity to narrow down the “news” items with more specific tag combinations. The ultimate goal is to capture the remaining relevant columns from tagand tagidfrom the table tags.

I tried to make different equivalent attempts JOIN, but could not select all those remaining tagidin the news that have tags attached to them.

Here are my SQL EXPLAINSQL results if they point to another reason for the slowness that I skip:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
 1 | PRIMARY | tags | range | PRIMARY | PRIMARY | 4 | NULL | 55 | Using where; Using filesort
 2 | DEPENDENT SUBQUERY | news_tags | index_subquery | tagid | tagid | 4 | func | 26 | Using index; Using where
 3 | DEPENDENT SUBQUERY | news_tags | index | tagid | PRIMARY | 8 | NULL | 11 | Using where; Using index

, : , BOTH 16 32, 16 32. .

+3
3
SELECT DISTINCT tags.tagid, tags.tag
FROM
       tags                             -- tags from the ...
  JOIN news_tags AS n0 USING (tagid)    -- ... news items tagged with ...
  JOIN news_tags AS n1 USING (newsid)   -- ... tagid = 16 and ...
  JOIN news_tags AS n2 USING (newsid)   -- ... tagid = 32
WHERE
  n1.tagid = 16 AND n2.tagid = 32
  AND tags.tagid NOT IN (16,32)         -- not the tags we already know about
ORDER BY tags.level, tags.tagid
+2

. sql OP, , .

SELECT DISTINCT t.tagid, t.tag FROM tags AS t
JOIN            news_tags AS nt1 USING (tagid) 
JOIN            news_tags AS nt2 USING (newsid)
WHERE           nt2.tagid IN (16, 32) AND t.tagid NOT IN (16, 32) 
GROUP BY        nt2.newsid HAVING COUNT(nt2.newsid)>=2
ORDER BY        t.level, t.tagid
+1

, , JOINS IN:

SELECT tags.tagid,tags.tag FROM tags 
INNER JOIN (SELECT DISTINCT news_tags.tagid FROM news_tags
INNER JOIN (SELECT newsid FROM news_tags WHERE tagid IN (16,32) 
GROUP BY newsid HAVING count(newsid) >= 2) tagged_news 
ON news_tags.newsid = tagged_news.newsid 
WHERE news_tags.tagid NOT IN (16,32)) rem_tags
ON tags.tagid = rem_tags.tagid
ORDER BY level, tagid

, , , , eggyal, .

( ), eggyval SQL, SQL , . .

0

More articles:


All Articles