Optimizing mysql fulltext search

I want to make a search with fulltext i开发者_StackOverflow社区n 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).

I have a comprehensive plan for you to optimize MySQL for FULLTEXT indexing as thoroughly as possible

The first thing you should do is : Get rid of the stopword list

This has annoyed some people over the years because of being unaware that over 600 words are excluded from a FULLTEXT index.

Here is tabular view of those stopwords.

There are two ways to bypass this

Bypass Option 1) Create a custom stopword list.

You can actually submit to mysql a list of your preferred stopwords. Here is the default:

mysql> show variables like 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

OK, not let's create our stopword list. I usually set the English articles as the only stopwords.

echo "a"    > /var/lib/mysql/stopwords.txt
echo "an"  >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt

Next, add the option to /etc/my.cnf plus allowing 1-letter, 2-letter, and 3 letter words

[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

Finally, restart mysql

service mysql restart

If you have any tables with FULLTEXT indexes already in place, you must drop those FULLTEXT indexes and create them again.

Bypass Option 2) Recompile the source code

The filename is storage/myisam/ft_static.c. Just alter the C structure that holds the 600+ words so that it is empty. Having fun recompiling !!!

Now that the FULLTEXT config is solidified, here is another major aspect to consider:

Write proper refactored queries so that the MySQL Query Optimizer works right !!!

What I am now mentioning is really undocumented: Whenever you perform queries that do JOINs and the WHERE clause contains the MATCH function for FULLTEXT searching, it tends to cause the MySQL Query Optimizer to treat the query like a full table scan when it comes to searching the columns invoved in the FULLTEXT index. If you plan to query a table using a FULLTEXT index, ALWAYS refactor your query to have the FULLTEXT search return only keys in a subquery and connect those keys to your main table. Otherwise, the FULLTEXT index will put the MySQL Query Optimizer in a tailspin.

For further ideas regarding full-text search optimization in MySQL, see How to optimize MySQL Boolean Full-Text Search? (Or what to replace it with?) - C#