Mysql Full-Text Search - Searching for part of a keyword

Question

I've read about full-text search functions in mysql. But in these methods you have to search for exactly right spelled complete words.

For example if your text contains 'Bitdefender 2009' and you search for Bit, you get nothing

SELECT * FROM logs WHERE MATCH (log) AGAINST ('Bit 09' IN BOOLEAN MODE);

So are there any solution for this?

(Is there a technique which would let you search for misspelled keywords as well? for example you search for Bitdefedner)

Answer 1

You probably need the help of LIKE, REGEXP, AGAINST or SOUNDEX functions.

Have a look at the following:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex

Answer 2

You could use the * wildcard AGAINST ('Bit* 09' IN BOOLEAN MODE)

For misspelt keywords you need a separate spellcheck phase.

Answer 3

You could also turn to Lucene or other specialized search engine mentioned in http://stackoverflow.com/questions/553055/best-full-text-search-for-mysql