优化mysql全文搜索 [英] Optimizing mysql fulltext search

问题描述

我想在我的网络中使用全文进行搜索。我需要使用分页进行搜索。我的数据库有超过50,000行/每个表。我改变了我的表格，并使（title，content，date）成为索引。该表总是更新，仍然有一个自动增加的列 id 。并且最新的日期总是在表格的最后。

I want to make a search with fulltext in my web. I need the search with a pagination. my database have 50,000+ rows/per table. I have alter my table and make (title,content,date) to be index. the table is always update, there still have a column id which is automatic increase. and the latest date is always at the end of table.

date  varchar(10)
title  text
content  text

但是整个查询时间将会成本 1.5+秒。我通过谷歌搜索了很多文章，有些人写道，只有限制 Index 字段的单词长度可以帮助搜索更快。但作为一个 text 类型，它不能改变一定的长度（我试过 ALTER TABLE table_1 CHANGE title title TEXT（500）CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ，不工作）

but whole query time will cost 1.5+ seconds. I search the many articles via google, some wrote that only limit Index field word length can help the search more quickly. but as a text type, it can not alter a certain length like that( i have tried ALTER TABLE table_1 CHANGEtitletitleTEXT(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, not work)

date  varchar(10)
title  text(500)
content  text(1000)

所以，除 Sphinx 和第三部分脚本。如何优化全文搜索只有SQL？在这里查询代码：

so, Except Sphinx and third part script. how to optimization fulltext search with only sql? query code here:

(SELECT 
title,content,date 
FROM table_1 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
UNION 
(SELECT 
title,content,date 
FROM table_2 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
Order By date DESC

谢谢。

推荐答案

基于问题的后续行动评论，你的列上有一个btree索引，而不是一个全文索引。

Based on the question's follow-up comments, you've a btree index on your columns rather than a full text index.

对于匹配搜索的MATCH（标题，内容），您需要：

For MATCH (title,content) against search, you would need:

CREATE FULLTEXT INDEX index_name ON tbl_name (title,content);

我不确定它会接受那里的日期字段（后者可能不相关反正）。

I'm not sure it'll accept the date field in there (the latter is probably not relevant anyway).

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