mysql Concat vs OR

Question

I was trying to find the best way to search multiple columns for the occurence of a word. Ofcourse an option would be to add multiple OR's in the query:

SELECT * FROM table WHERE column1 LIKE %term1% OR column2 LIKE %term1% OR......

Yet I though this made the query quite long, especially when there are multiple terms

In my search I came across the CONCATfunction in MYSQL. This seems to allow me to make one search after concatenating the columns (right?):

SELECT * FROM table WHERE CONCAT(column1,column2,....) LIKE %term1% OR CONCAT(column 1,column2,...) LIKE %term2% ....

This has the advance of being easier to read and maintain

Because I do not yet understand the full use of CONCAT I do not know or this is a correct way to use it. I was wondering what is the correct and most efficient way to achieve this search. (note: I am also planning on having it sort as stated in this stackoverflow question: mysql SORT BY amount of unique word matches, maby there are problems while using a method with this?)

EDIT:

Maby it would be easier to just explain my final wish: In php i have an array of single words. I wish select all rows from my database where 1 of the words occurs in one of multiple colmuns and then sort them by relevance (or even add extra importance to some columns). (note that there are only a few thousands (less than 5000) rows to search)

Answer 1

Since i tought this was an interesting point, i tought the best would be good to give it a try.

Well, after your edit, it seems you don't have that much data, but i'll still post the result :

---

EDIT : This was my first try over MySQL testing, and, as @Alma Do said, those results "may be just fluctuation".

---

First query (on 90000 rows) road and road2 are VARCHAR(100):

SELECT * FROM adress WHERE CONCAT(road1,road2) LIKE '%test%'; 0.0503 sec

SELECT * FROM adress WHERE road1 LIKE '%test%' OR road2 LIKE '%test%'; 0.0710 sec

It seems indeed multiple LIKE is much more resourcefull. I'm not that good with MySQL resource verification, so i didn't go further on this, guess it should still give an idea.

The main problem i see is if CONCAT could create wrong matches :

SELECT CONCAT('fooa','bfoo') with LIKE '%ab%' would work, while 'fooa' LIKE '%ab% OR 'bfoo' LIKE '%ab% OR wouldn't.

For 500 rows, i don't think you would have resource issues, so i would have just gone with what 'has the advance of being easier to read and maintain'.

Hope it helps