Match String on Multiple Columns

Posted by in Software

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 first_name and last_name columns one at a time and I decided to try and search both. The result is something like:

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):

Note: MySQL has a CONCAT_WS(" ", first_name, last_name) to concatenate strings.

HTH,


A little experiment: If you find this post and ad below useful, please check the ad out :-)