Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Optimizing mysql fulltext search

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.

like image 797
yuli chika Avatar asked May 20 '11 11:05

yuli chika


People also ask

What is fulltext search in MySQL?

MySQL FULLTEXT SEARCH (FTS) Full-text search is a searching technique used to examine all words in the document that might not perfectly match the search criteria. The records contain textual data like product descriptions, blog posts, articles, etc.

What is advantage of fulltext over like for performing text search in MySQL?

Like uses wildcards only, and isn't all that powerful. Full text allows much more complex searching, including And, Or, Not, even similar sounding results (SOUNDEX) and many more items.

Can multi column fulltext indexes be used if so when?

The columns in the fulltext index can be placed in any sort of order, but all the columns as specified when creating the fulltext index must be referenced in the match() query.

How do I create a full text search in MySQL?

The basic query format of full-text searches in MySQL should be similar to the following: SELECT * FROM table WHERE MATCH(column) AGAINST(“string” IN NATURAL LANGUAGE MODE); When MATCH() is used together with a WHERE clause, the rows are automatically sorted by the highest relevance first.


1 Answers

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

like image 136
Denis de Bernardy Avatar answered Sep 18 '22 22:09

Denis de Bernardy