Geek Answers Handbook

MySQL INTERSECT via connection table?

So, essentially, I have two tables containing URLS and TAGS, with a has-and-belongs-to-many relationship between them through TAGS_URLS join tables.

A simple query to search for URLs by tags would look like this:

SELECT urls.id FROM urls 
  INNER JOIN tags_urls ON urls.id=tags_urls.url_id
  INNER JOIN tags ON tags_urls.tag_id=tags.id 
WHERE tags.tag IN ("sample","tag","list");

However, I am trying to recover the intersection of the entire URL containing the entire set of tags. Ie, only the URL containing the tag "sample" AND "tag" AND "list".

I have a working request, but I cannot complete the request in less than 30 seconds.

SELECT a.id
  FROM
    (SELECT DISTINCT urls.id FROM urls
      INNER JOIN tags_urls ON tags_urls.url_id=urls.id INNER JOIN tags ON tags.id=tags_urls.tag_id
      WHERE tags.tag = 'sample') a
  JOIN
     (SELECT DISTINCT urls.id FROM urls
      INNER JOIN tags_urls ON tags_urls.url_id=urls.id INNER JOIN tags ON tags.id=tags_urls.tag_id
      WHERE tags.tag = 'list') b
  ON a.id = b.id;

The result set is correct, but the performance is terrifying.

Redis URL-, , - .

SINTER "tag-sample" "tag-list"

MySQL Redis SINTER?

+5
2

100%, , . . ( ), temp- , . , , , . , , , , , , .

. , , :

select urls.id from urls
inner join tags_urls tu1 on tu1.url_id = urls.id
inner join tags t1 on tu1.tag_id = t1.id and t1.tag = 'sample'
inner join tag_urls tu2 on tu2.url_id = urls.id
inner join tags t2 on t2.id = tu2.tag_id and t2.tag = 'list'

tag_urls , . , , .

, .

+1

sql . , Sql Server , . , MySql. , , "" "", . , .

0

More articles:


All Articles