TL;DR: Use concatenation, Luke!
I had to search a name for an user in a table containing the first and last names in separate columns. After a while I got tired on looking up the
last_name columns one at a time and I decided to try and search both. The result is something like:
SELECT * FROM users WHERE LOWER(first_name || ' ' || last_name) LIKE '%laur%';
I have to do the
LOWER() call because the names are provided by user input.
At first I’ve been worried about performance, but since this is not a recurrent query, it’s acceptable (0.25s on average from a table with 100k records).
The Query can be optimised a bit more since the concatenated string is not displayed (0.2s on average):
SELECT * FROM users WHERE LOWER(first_name || last_name) LIKE '%laur%';
Note: MySQL has a
CONCAT_WS(" ", first_name, last_name) to concatenate strings.