Question

Can anyone tell me ways to do this kind of search in a database?

我看这些表格:

posts (id, tags_cache)
tags (id, name)
posts_tags (post_id, tag_id)

The user enters a search query (say "water blue") and I want to show the posts that have both tags. The only way I can think of to search is using FIND_IN_SET, this way:

SELECT p.*, GROUP_CONCAT(t.name) AS tags_search
FROM posts p
LEFT JOIN posts_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id
HAVING FIND_IN_SET( water , tags_search) > 0
AND FIND_IN_SET( blue , tags_search) > 0

<代码>posts.tags_cachetext栏列有其所归属标签的名称和名称(water:15 Blue:20)。

To avoid JOINs by using this column for search, I ve tried LIKE and INSTR but these will give inexact results since you can search for "ter" and you ll gets posts tagged water and termal for example. I ve also tried REGEXP which gives exact results, but it s a slow process.

I can t use MATCH as tables use InnoDB.

So... is or are there other ways to accomplish this?

<><>>

I forgot to mention that the user could search for many tags (not just 2), and even exclude tags: search posts tagged water but not blue . With FIND_IN_SET this works for me:

HAVING FIND_IN_SET( water , tags_search) > 0
AND NOT FIND_IN_SET( blue , tags_search) > 0

<>strong>[Edit2]

我进行了一些性能测试(即只检查问答时间、时间顺序),正如“精伯”建议的那样,结果如下:

muists | Bill K | ypercu | includes:excludes
--------------------------
0.0137 | 0.0009 | 0.0029 | 2:0
0.0096 | 0.0081 | 0.0033 | 2:1
0.0111 | 0.0174 | 0.0033 | 2:2
0.0281 | 0.0081 | 0.0025 | 5:1
0.0014 | 0.0013 | 0.0015 | 0:2

I don t know if this info is valid resource... But it shows that ypercube s method with a JOIN per tag is the quickest.

Answer 1

如果你提出额外要求,除某些方面外,你可以采用下一个办法。它将给你所有既拥有水和蓝tag,又没有黑人、白人或红色的岗位:

SELECT p.*
FROM posts p
  INNER JOIN posts_tags pt1 ON p.id = pt1.post_id 
  INNER JOIN tags t1 ON pt1.tag_id = t1.id
  INNER JOIN posts_tags pt2 ON p.id = pt2.post_id
  INNER JOIN tags t2 ON pt2.tag_id = t2.id
WHERE (t1.name, t2.name) = ( water ,  blue )          --- include
  AND NOT EXISTS
      ( SELECT *
        FROM posts_tags pt
          INNER JOIN tags t ON pt.tag_id = t.id
        WHERE p.id = pt.post_id 
          AND t.name IN ( black ,  white ,  red )     --- exclude
      )

Answer 2

I don t understand why you don t want to use JOINs nor why you re trying to use LEFT JOINs. You re looking for things that are there (rather than might be there) so get rid of the LEFT JOINs and just JOIN. And get rid of the tags_cache column, you re only asking for trouble with that sort of thing.

与此类似,是你回顾的:

select p.id
from posts p
join posts_tags pt on p.id = pt.post_id
join tags t on pt.tag_id = t.id
where t.name in ( water ,  blue )
group by p.id
having count(t.id) = 2

中文不变。

And if you want to exclude certain tags, you could just add that to the WHERE clause like this:

select p.id
from posts p
join posts_tags pt on p.id = pt.post_id
join tags t on pt.tag_id = t.id
where t.name in ( water ,  blue )
  and p.id not in (
    select pt.post_id
    from posts_tags pt
    join tags t on pt.tag_id = t.id
    where t.name in ( pancakes ,  eggs ) -- Exclude these
)
group by p.id
having count(t.id) = 2

Answer 3

寻找与不同行各处若干条件相匹配的员额是一个共同的问题。

这里有两个途径:

SELECT p.*
FROM posts p
INNER JOIN posts_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ( water ,  blue )
GROUP BY p.id
HAVING COUNT(DISTINCT t.name) = 2;

或:

SELECT p.*
FROM posts p
INNER JOIN posts_tags pt1 ON p.id = pt1.post_id
INNER JOIN tags t1 ON pt1.tag_id = t1.id
INNER JOIN posts_tags pt2 ON p.id = pt2.post_id
INNER JOIN tags t2 ON pt2.tag_id = t2.id
WHERE (t1.name, t2.name) = ( water ,  blue );

评论和编辑:

“HAVING”方案的问题是,它必须做一个表扫描,在表格中搜索每一行。这往往比初专干事网络要慢得多(如果你有适当的指数)。

为了支持tag除外条件,我是这样写的:

SELECT p.*
FROM posts p
INNER JOIN posts_tags pt1 ON p.id = pt1.post_id
INNER JOIN tags t1 ON pt1.tag_id = t1.id AND t1.name =  water 
LEFT OUTER JOIN (posts_tags pt2 
INNER JOIN tags t2 ON pt2.tag_id = t2.id AND t2.name =  blue )
  ON p.id = pt2.post_id
WHERE t2.id IS NULL;

避免使用JOIN,因为你在某个地方读过它,是毫无意义的。你们必须理解的是,JOIN是一个有关数据库的基本业务,在工作要求时,你应当使用这一数据库。

友情链接