MySQL搜索查询优化: Match...Against与%LIKE%

Question

我想建立我的第一个真正的搜索功能。我在谷歌上搜索了很长一段时间，但是我并没有下定决心去理解所有的事情。

我的数据库由三个InnoDB表组成：

• 产品:包含产品信息。列：proID (主列，自动增量)，content (包含数百字)，title，author，year，以及与搜索查询无关的一堆其他列。行: 100至2000年。
• 类别:包含类别信息:列：catID (主，自动增量)，catName.行数: 5-30
• 产品分类:两者之间的联系。每个产品可以涉及多个类别。列：pcID (主列，自动增量)，catID，proID.排:产品数量的1-5倍。

我的搜索功能提供了以下内容。它们不需要填写。如果填写了多个查询，则最后一个查询将将它们与AND- query连接起来：

• 术语:搜索内容和标题字段。随机搜索，可以添加多个单词，但每个词的搜索是分开的。最有可能的情况是，1匹配数据库应该足够一次命中(OR-query)
• 年份:搜索年份-产品栏。
• 类别:可从类别列表中选择。多重可能。表单返回所选类别的catID。1与数据库匹配应足以满足命中(或查询)
• 作者:产品的author-column搜索

正如您可能已经注意到的，当选择一个类别时，表、产品和产品类别将结合在一起进行搜索查询。两者之间也有一个外键集。

要清理这些关系，这是一个应该如何解释关系的例子(不搜索年份！)：

搜索WHERE (products.content = term 1 OR products.content = term 2 OR products.title = term 1 OR products.title = term 2 ......) AND (products.author = author) AND (productscategories.catID = catID1 OR productscategories.catID= catID2 ......)

还请注意，我创建了一个分页系统，它只显示每个“页面”上的10个结果。

我遇到的问题是:我希望优化这个搜索查询，但找不出哪一种方式是最好的。

大多数情况下，我发现谷歌使用LIKE %% mysqli查询。然而，有些人使用了MATCH...AGAINST。我似乎真的很喜欢最后一个，因为我阅读了它可以根据相关性进行排序，而且它似乎使创建查询变得更加容易(1匹配术语值，而不是大量的LIKE %%与OR相结合)。不过，我似乎只会在“搜索场”这个词上使用它。但是对于MATCH...AGAINST，我需要一个MyIsam表(对吗？)，在这个表中，我不能使用外键来防止数据库中的错误。

MATCH...AGAINST示例(没有年份字段、类别字段并没有加入产品和产品分类)：

SELECT *,MATCH (content,title) AGAINST ('search terms' IN BOOLEAN MODE) AS relevance 
FROM products WHERE (MATCH (content,title) AGAINST ('search terms' IN BOOLEAN MODE)) AND
author='author' SORT BY relevance LIMIT 10

%LIKE%示例(没有年份字段、类别字段而没有加入产品和产品分类)和遗憾的是没有关联排序：

SELECT * FROM products WHERE
(content LIKE '%term1%' OR content LIKE '%term2' OR title LIKE '%term1%' OR title LIKE '%term2%')
AND (author='author') SORT BY title LIMIT 10

我可以通过使用CASE进行关联排序，并在标题或内容中添加“点数”？或者，这会使查询过于繁重而影响性能吗？

那么，做这种查询的最好方法是什么呢？使用Innodb和LIKE，还是切换到MyIsam并使用MATCH...AGAINST进行排序？

Answer 1

你不必切换到MyIsam。MySQL5.6及更高版本支持全文索引。

我通常建议使用全文索引。在列标题、作者、年份上创建全文索引

然后，您可以同时在所有3个上运行一个全文查询，并将其应用于布尔模式，从而真正缩小搜索范围。当然，这是你必须自己决定的事情，但全文中的选项更多。

但是，如果您正在运行在范围、日期或简单字符串之间生成的查询。那么标准索引更好，但是对于在不同列中搜索的tekst来说，全文索引是可行的！

读这个：http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html