mySQL LIKE query

Question

I'm trying to search for a string in my DB , but it should be able to match any word and not the whole phrase.

Suppose, a table data has text like a b c d e f g. Then if I search for d c b it should be able to show the results.

field LIKE '%d c b%' doesn't work in this way.

Can someone suggest a more robust way to search, possible showing the relevance counter also.

I don't mind using PHP also for the above, but prefer to do the search at DB level.

Answer 1

For best results, you need to create FULLTEXT index on your data.

CREATE TABLE mytable (id INT NOT NULL, data TEXT NOT NULL, FULLTEXT KEY fx_mytable_data) ENGINE=MyISAM

SELECT  *
FROM    mytable
WHERE   MATCH(data) AGAINST ('+word1 +word2 +word3' IN BOOLEAN MODE)

Note that to index one-letter words (as in your example), you'll need to set ft_min_word_len to 1 in MySQL confguration.

This syntax can work even if you don't have an index (as long as your table is MyISAM), but will be quite slow.