full text search - mysql partial word searching

Question

I want to search like the below query but it is not working in MySQL

 SELECT * FROM products WHERE MATCH (title,description) 
 AGAINST ('test' IN BOOLEAN MODE); 

I want below output

testproduct, producttest

Can anybody help me on this..?

Answer 1

currently, only support prefix, not support suffix:

SELECT * FROM products WHERE MATCH (title,description) 
 AGAINST ('test*' IN BOOLEAN MODE); 

http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html

By the way, MySQL full text search will be bottleneck when the website request is large, and it is vulnerable and unstable for some special search word, thus often lead to slow query and database crash. Use Elastic Search as soon as possible.