全文搜索不返回总数 [英] fulltext search not returning total count

问题描述

我正在用mysql进行全文搜索。我的数据保存在两个表中，因此我必须在每个表上运行单独的匹配项，并将相关性添加到一起，如您在我的MySQL语句中所见。

问题是我正在试图获得COUNT个邮件表中有问题的帖子。然而，只有在问题表中找到匹配项时才会返回给我，而不是在帖子表中找到匹配项的情况下呢？任何想法为什么？

请告诉我更多信息是必要的。谢谢，

  SELECT问题。 *，
 posts.post，
 COUNT（posts.post）-1 AS total_answers，
 posts.votes，
 posts.id AS post_id，
 posts.created ，
 users.id AS user_id，
 users.username，
 users.rep，
 MATCH（questions.title）AGAINST（'{$ keywords}'）AS title_relevance，
 MATCH（posts.post）AGAINST（'{$ keywords}'）AS post_relevance 
 FROM questions 
 LEFT JOIN posts on questions.id = posts.question_id 
 LEFT JOIN users on questions .user_id = users.id 
反对（'。$关键字}'）
或匹配（posts.post）反对（'{$关键字}'）
 GROUP BY questions.id 
 ORDER BY（title_relevance + post_relevance）DESC 
  

找到答案。

  SELECT问题。 *，posts.post，posts.question_id AS QID，（
 
 SELECT COUNT（posts.post）
 FROM posts 
 WHERE question_id = QID 
）AS total_answers， posts.votes，posts.id AS post_id，posts.created，users.id AS user_id，users.username，users.rep，
 MATCH（
 questions.title 
）
反对（
'人类'
）AS title_relevance，
 MATCH（
 posts.post 
）
反对（
'人类'
）AS post_relevance 
 FROM问题
 LEFT JOIN帖子在questions.id = posts.question_id 
 LEFT JOIN用户在questions.user_id = users.id 
 WHAT MATCH（
 questions.title 
）
反对（
'人类'
）
或匹配（
 posts.post 
）
反对（
'人类'
）
 GROUP BY questions.id 
命令（
 title_relevance + post_relevance 
）DESC 
限制0 ，30 
  

解决方案

确保有全文索引创建在每张桌子上。

MySQL无法跨多个表创建全文索引。因此，您应该在每个表格上使用索引，并进行连接以检索与您的文本匹配的行。

Desperate for help on this, please help!

I am running a fulltext search with mysql. My data is held in two tables, so I have to run separate matches on each table and add the relevancy together as you can see in my MySQL statement.

The problem is that I am trying to get a COUNT for the total posts a question has in my post table. However this is only returned to me when the match is found in the question table, not if the match is found in the posts table? Any idea why?

Please tell me in more information is needed. Thanks,

SELECT questions. * , 
     posts.post, 
     COUNT(posts.post) -1 AS total_answers, 
     posts.votes, 
     posts.id AS post_id, 
     posts.created, 
     users.id AS user_id, 
     users.username, 
     users.rep, 
     MATCH (questions.title) AGAINST ( '{$keywords}') AS title_relevance,
     MATCH (posts.post) AGAINST ( '{$keywords}') AS post_relevance
FROM questions
     LEFT JOIN posts ON questions.id = posts.question_id
     LEFT JOIN users ON questions.user_id = users.id
WHERE MATCH (questions.title) AGAINST ( '{$keywords}')
    OR MATCH (posts.post) AGAINST ( '{$keywords}')
GROUP BY questions.id
ORDER BY (title_relevance + post_relevance) DESC

Found the answer.

SELECT questions. * , posts.post, posts.question_id AS QID, (

SELECT COUNT( posts.post ) 
FROM posts
WHERE question_id = QID
) AS total_answers, posts.votes, posts.id AS post_id, posts.created, users.id AS     user_id, users.username, users.rep, 
MATCH (
questions.title
)
AGAINST (
'humans'
) AS title_relevance, 
MATCH (
posts.post
)
AGAINST (
'humans'
) AS post_relevance
FROM questions
LEFT JOIN posts ON questions.id = posts.question_id
LEFT JOIN users ON questions.user_id = users.id
WHERE MATCH (
questions.title
)
AGAINST (
'humans'
)
OR MATCH (
posts.post
)
AGAINST (
'humans'
)
GROUP BY questions.id
ORDER BY (
title_relevance + post_relevance
) DESC 
LIMIT 0 , 30

解决方案

Make sure that there is a full text index created on each table.

MySQL can't make a fulltext index across multiple tables. Therefore you should use an index on each table, and do a join to retrieve the rows that match your text.

这篇关于全文搜索不返回总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！