Check if a list of other strings is %LIKE% another string

Question

Is there a short way to check if a list of strings is %LIKE% a given string?

For example:

book animal a

Would all satisfy this condition for "A Book about Animals"?

I know that I could write out Title Like '%book%' AND '%animal%' LIKE Title, etc. but that seems unwieldy.

I'm using this for an autocomplete, where I return results where every word is in the result.

Answer 1

You should look at http://lucene.apache.org/solr/, it let's you index your data, and then can give a REST API that you can call to populate an autocomplete dropdown.

Answer 2

As far as I know, you have two options:

1) Perform a query with lots of WHERE clauses like you have.

2) Maintain a copy of the column with all of the words sorted. Then you can sort the search tokens and perform the query:

SELECT * FROM Table WHERE Title_sorted LIKE "%a%animal%book%"

Answer 3

MySQL has a full-text search index, I recommend you look into that to get efficient results. Going with Lucene might be overkill for just an autocomplete.

Answer 4

mate... make the searchable columns into "fulltext" colums and then do

SELECT * FROM books WHERE MATCH(fulltext_column) AGAINST('animal book a')

best way. :) see http://devzone.zend.com/article/1304