SQL search for words in any order

Question

Given this mockup:

+-----------+-------------+---------------------------+
|  item_id  |  item_name  |  desc                     |
+-----------+-------------+---------------------------+
|  1        |  Product 1  |  lorem ipsum dolor...     |
|  2        |  Product 2  |  lorem mauris eu...       |
|  3        |  Product 3  |  scelerisque sagittis...  |
|  4        |  Product 4  |  lorem dolor ipsum...     |
|  5        |  Product 5  |  ipsum dolor lorem...     |
+-----------+-------------+---------------------------+

And I want to search all of the products that contain the words lorem ipsum in either item_name or desc. Additionally, any words can appear between lorem and ipsum, and lorem and ipsum can appear in any order. Basically, this search would return items 1, 4, and 5

Now, I know I could accomplish this with:

SELECT * FROM items
WHERE (item_name LIKE 'lorem%ipsum'
       OR desc LIKE 'lorem%ipsum')
OR (item_name LIKE 'ipsum%lorem'
    OR desc LIKE 'ipsum%lorem')

But if my search term is longer (ie. lorem ipsum dolor sit amet, consectetur adipiscing elit), I feel like it could become a bit ridiculous with the number of OR added to the query. Is there an easier/more efficient way to handle this?

Answer 1

this sort of search requirement sounds a good candidate for full text search.

Full text search is (or at least can be) more of a "search engine" like search as opposed to the traditional sql like searches. With full text searching, the order of the words being searched for does not matter, and depending on the RDBMs some full text searching capabilities allow for synonym lookup, as well as noise word filtering.

In (i believe) most cases, full text searching is significantly faster than a like search. Here is an article on getting started with full text search in mysql.

Example mySql full text search syntax:

select *
from items
where match(item_name) against ('+lorem +ipsum' in boolean mode)

Full text searching does have certain requirements (which are gone into detail in the links in the article). I've not personally worked with mysqls full text search, or I'd list out the steps. Should be enough to get you started though if you wanted to go in that direction.

Answer 2

I think it is simpler to look for each word separately:

SELECT *
FROM items
WHERE (item_name like '%lorem%' and item_name like '%ipsum%') or
      (`desc` like '%lorem%' and `desc` like '%ipsum%');

This generalizes most easily to more words and to more columns.

If you like, you can concatenate the values together. However, if you want both values in the same field, then the separate logic is clearer.

Also, if you are really looking for words, then full text search is a good option and will have much better performance.