MySQL 视图上的全文索引或更正在连接表中搜索的方式

Question

尽管这个话题已经被广泛讨论，但它让我非常困惑，我无法提炼出正确的答案。你能帮助我朝正确的方向前进吗？谢谢。

我有这样的设置：表page通过参考表widget_to_page连接到表widget：

page (p)
<代码> sid |标题 |内容
小部件 (w)
sid |内容
widget_to_page (wtp)
sid | page_sid | widget_sid

创建了全文索引

我已经在 p.title、p.content、w.content接下来 ，我想要页面小部件组合：

SELECT *, MATCH(p.title, p.content, w.content) AGAINST("'.$keyword.'") AS score 
FROM page p, widget w, widget_to_page wtp
WHERE MATCH(p.title, p.content, w.content) AGAINST("'.$keyword.'")
AND p.sid = wtp.page_sid
AND w.sid = wtp.widget_sid
ORDER BY score DESC

一些备注：

1. 这显然不起作用...:-)
2. 我一直在阅读如果我使用此语法来连接表（或使用 JOIN）语句，它会导致 MySQL 忽略全文索引。正确的方法是什么？

我尝试过的另一种方法是使用以下语句创建 MySQL 视图：

SELECT * 
FROM page p, widget w, widget_to_page wtp
WHERE p.sid = wtp.page_sid
AND w.sid = wtp.widget_sid

但这似乎也不起作用。有些人谈到在此特定视图上创建全文索引，但我在 PHPMyAdmin 中找不到任何选项来执行此操作，而且我不知道这是否可能。

简而言之......这种情况下的最佳做法是什么？谢谢。

Answer 1

FULLTEXT 索引可以包含多个列，但这些列必须位于同一个表中。因此，您将无法在单个索引中覆盖 2 个表中的这 3 列。您至少需要 2 个索引才能完成此任务。

此外，FULLTEXT 索引仅适用于 MyISAM 存储引擎，因此如果您使用的是 InnoDB 等事务引擎，则这将不起作用。您应该阅读手册以了解全文索引的其他功能和限制（包括布尔模式、停用词等）：

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

假设你使用的是MyISAM，你可以创建1个全文索引(p.title,p.content) 和 (w.content) 上的另一个全文索引，然后分别查询这两个索引并使用 UNION ALL 组合结果。

生成的查询将类似于以下内容，但您需要对其进行调整以满足您的特定要求：

select page_sid, page_title, sum(score) as total_score
from 
(
SELECT p.sid as page_sid,p.title as page_title,NULL as widget_sid, 
  MATCH(p.title, p.content) AGAINST("'.$keyword.'") AS score 
FROM page p
WHERE MATCH(p.title, p.content) AGAINST("'.$keyword.'")
UNION
SELECT p.sid as page_sid,p.title as page_title,w.sid as widget_sid, 
  MATCH(w.content) AGAINST("'.$keyword.'") AS score 
FROM page p
  inner join widget_to_page wtp on p.sid = wtp.page_sid
  inner join widget w on w.sid = wtp.widget_sid
WHERE MATCH(w.content) AGAINST("'.$keyword.'")
) as sub_query
group by page_sid
ORDER BY total_score DESC