CODEWITHSUNDEEP
mysql
h0rn3t
h0rn3t

Reputation: 3

MYSQL Search by list of IDs using WHERE

I have 3 tables: 'videos', 'tags' and 'links'. Here is an example of links table:

mysql> select * from links;
+----+-------+---------+
| id | tagid | videoid |
+----+-------+---------+
| 25 |     6 |      35 |
| 24 |     5 |       7 |
| 23 |     1 |       7 |
| 22 |     7 |       3 |
| 21 |     1 |       3 |
+----+-------+---------+
5 rows in set (0.00 sec)

To get tags list for each video I use this query:

SELECT v.id, GROUP_CONCAT(l.tagid) as tags FROM videos v LEFT JOIN links l ON l.videoid = v.id GROUP BY v.id;
+----+------+
| id | tags |
+----+------+
| 30 | NULL |
| 31 | 2    |
| 32 | 1,3  |
| 33 | 1    |
| 34 | 1,2  |
+----+------+
5 rows in set (0.02 sec)

But how can I search for videos, containing a list of tags? Currently I add HAVING clause at the end of query.

For example, I have three videos with tags '1', '1,4', '1,4,7'. In order to find videos which contain tags 1 and 4 I add HAVING sum(tagid = 1) > 0 AND sum(tagid = 4) > 0. It returns two last videos. This is a solution from similar questions. It would be more convenient to me to use WHERE clause, so I am looking for an answer with WHERE.

Upvotes: 0

Views: 108

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31792

As I understand, your actual working query is

SELECT v.id, GROUP_CONCAT(l.tagid) as tags
FROM videos v
LEFT JOIN links l ON l.videoid = v.id
GROUP BY v.id
HAVING sum(tagid = 1) > 0
   AND sum(tagid = 4) > 0

This (IMHO) is just fine as long as the tables are not too big. However with this HAVING condition your JOIN becomes logically an INNER JOIN. And if you only need the id of the videos, you can select l.videoid without touching the videos table.

SELECT l.videoid, GROUP_CONCAT(l.tagid) as tags
FROM links l
GROUP BY l.videoid
HAVING sum(tagid = 1) > 0
   AND sum(tagid = 4) > 0

But this requires a full table scan on the links table, which might be a performance issue on a big dataset. For a better performance you can try the following query:

SELECT l.videoid, GROUP_CONCAT(l.tagid) as tags
FROM links l
JOIN links l1 USING(videoid)
JOIN links l2 USING(videoid)
WHERE l1.tagid = 1
  AND l2.tagid = 4

Given indexes on links(tagid, videoid) and links(videoid, tagid) the execution plan should be:

  • Find all rows in links (l1) with tagid = 1 (search in index (tagid, videoid))
  • Find all rows in links (l2) with tagid = 4 and the same videoid as in l1 (search in index (tagid, videoid)) and skip all rows without a match.
  • Find all rows in links (l) with the same videoid as in l1 and l2 and group them by videoid (use index (videoid, tagid) for search and GROUP BY)

If you need more than just the videoid you can still join the videos table

JOIN videos v ON v.id = l.videoid

and select what ever you need from that table.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You could filter for with a in clause the tag you need and having count(disctinct tagid) equals to number of tag you need eg for two tagid named tagid1, tagid2 

SELECT v.id, GROUP_CONCAT(l.tagid) as tags 
FROM videos v 
where l.tagid in(  tagid1, tagid2)
LEFT JOIN links l ON l.videoid = v.id GROUP BY v.id
having count(dictinct tagid) = 2;

otherwise if you need also the video that contain the 2 tags but also the others you should remove the having clause

SELECT v.id, GROUP_CONCAT(l.tagid) as tags 
FROM videos v 
where l.tagid in(  tagid1, tagid2)
LEFT JOIN links l ON l.videoid = v.id GROUP BY v.id

Or you can use a in clause the video tha match tha tag 

SELECT v.id, GROUP_CONCAT(l.tagid) as tags 
FROM videos v 
where v.id in (
    select videos.id 
    from videos
    INNER JOIN links on links.videoid = video.id 
          and links.tagid in (  tagid1, tagid2)
) 
GROUP BY v.id

Upvotes: 1

Related Questions