Searching for keywords in two MySQL columns

Question

I have a user table with columns named *first_name* and *last_name*.

SELECT * 
    FROM users 
WHERE first_name LIKE '%keywords%' 
    OR last_name LIKE '%keywords%'

Using the above, if I search for "John" or for "Doe" I'll get a hit.

But if I search for "John Doe" I will get 0 results. How can I search MySQL in a way that will match "first_name last_name" rather than just one or the other?

Answer 1

Try:

SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) LIKE '%keywords%';

Answer 2

One solution is to split the keywords in your application, and then build the query as follows:

-- Split "John Doe" -> "John", "Doe"

SELECT * FROM users WHERE
(
    (
         first_name LIKE '%keyword_1%' OR
         last_name LIKE '%keyword_1%'
    )
    AND
    (
         first_name LIKE '%keyword_2%' OR
         last_name LIKE '%keyword_2%'
    )
)

The above would also match "Doe Joe", not just "Joe Doe". This will get bigger if you are searching into more columns, as you will have to add an "AND block" for each keyword.

In addition, this comes with a big performance cost, since such a query is unable to use indexes on first_name and last_name, if there are any. A better approach would be to use Full Text indexing.

Answer 3

Have you tried using full-text indexing?