using LIKE on 2 fields

[ProblemHelpPlease]

I am trying to find the most efficient way to search 2 columns for a matching query. The problem is that the match can be in either column or only match when both columns are combined, for examplecolumn 1 containslong greenshort redlong yellowcolumn 2 containsspotty sockscotton socksitchy socksI am current using LIKE '%$search%' in my query on both fieldsSELECT * FROM table WHERE column1 LIKE '%$search%' OR column2 LIKE '%$search%'This works if I search for 'long' or 'long green' or 'green' or 'spotty' etc but not if I search for 'long green spotty socks' or 'long socks'.I can't combine the fields or create a new field from the 2 seperate fields physically in the database as it would cause too many issues with other scripts.I know I could potentially use some kind of JOIN but need to find the mose efficient way as this code runs on a livesearch basis.Thanks in advance

[justsomeguy]

You need to break up the words in the search term and search for each word individually in both fields.

[ProblemHelpPlease]

I had thought of doing it that way but was hoping that mysql would provide a faster way of checking the data. As this is used in a livesearch it needs to return results instantly as the user performs the search so i can't have a delay in the results appearing. The table in question will have around 4000 to 5000 rows, do you think that the delay in searhcing by word would cause a delay with this size of table.

[justsomeguy]

As this is used in a livesearch it needs to return results instantly as the user performs the search so i can't have a delay in the results appearing.

Nothing is instantaneous, everything has a delay. The question is how much.

The table in question will have around 4000 to 5000 rows, do you think that the delay in searhcing by word would cause a delay with this size of table.

It will be worth a test. You may be able to speed things up by using indexes or a fulltext search.