SQL search for words in any order

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.

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.