mysql全文搜索优化_优化mysql全文搜索

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

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)

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

Thanks.

解决方案

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

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).