MySQL Search Query on two different fields

Question

I need to search on two fields using LIKE function and should match also in reverse order. My table uses InnoDB which dont have Full text search.

Consider the following case:

I have users table with first_name and last_name column. On it, there is a row with the following value:

{
    first_name: 'Ludwig',
    last_name: 'van Beethoven',
}

Cases:

1. Can search "Ludwig van Beethoven"
2. Can search "Beethoven Ludwig"
3. Can search "Ludwig"
4. Can search "Beethoven"

I tried this SQL statement but no luck.

SELECT CONCAT(first_name, ' ', last_name) as fullname 
  FROM users 
 WHERE fullname LIKE '%Ludwig van Beethoven%';

Answer 1

You need to re-state the concat expression in your where clause.

 SELECT CONCAT(first_name, ' ', last_name) as fullname 
      FROM users 
     WHERE CONCAT(first_name, ' ', last_name) LIKE '%doe%';

Unfortunately "as" just create a column alias, not a variable that you can use elsewhere.

Answer 2

The Main Thing

Make sure you have a compound index on first_name and last_name. Otherwise, it's really easy to end up doing a full table scan regardless of how you approach this. So if you don't already have one, create one:

CREATE INDEX users_firstandlast ON users(first_name, last_name);

Syntax Options

Once that index is in place, you have some options:

Option 1: As Willis Blackburn said, repeat the CONCAT in your WHERE clause (because AS doesn't create a name you can use in the WHERE clause):

SELECT CONCAT(first_name, ' ', last_name) as fullname 
FROM users 
WHERE CONCAT(first_name, ' ', last_name) LIKE '%doe%';

Use EXPLAIN to check in your specific situation, but in my tests it says it uses the compound index, even though you're using a function in the WHERE clause.

Option 2: In this particular case, you can always just use two LIKE s in your WHERE clause:

SELECT CONCAT(first_name, ' ', last_name) as fullname 
FROM users 
WHERE first_name LIKE '%doe%' or last_name LIKE '%doe%';

Again this can make use of the compound index (whereas it won't make use of individual indexes on the first_name and last_name columns -- it would if you weren't leading with a wildcard, but according to EXPLAIN [and your mileage may vary, always check], in that case it goes with the table scan).

Option 3 In his answer, Andy says you can use HAVING for this. My read of the MySQL manual suggests it will first build the result set, and only then apply HAVING at the very end before sending it to the client, and so I'd be leery of this. But, in my quick and dirty tests, EXPLAIN tells me that if you have the compound index I mentioned above, the HAVING version does an index search, not a table scan. If your tests with real data bear that out, that may be a good option for you. This use of HAVING in this way is a MySQL extension (not standard), but then again, so is CONCAT so we're already into MySQL-specific stuff. :-) But again, double-check in your real life environment.

Conclusion

Create the index if you don't already have it, then I'd go with Option 2 if it's remotely a possibility; otherwise, option 1 unless you can find (or Andy can provide) a reference for the HAVING thing not building a massive interim result set (it would be really cool, if non-standard, if it didn't). Regardless, check with EXPLAIN, and test, in your specific environment.

Answer 3

When you CONCAT() two columns the LIKE become case sensitive. So this should find you results but isn't optimal for performance:

SELECT CONCAT(first_name, ' ', last_name) AS fullname FROM users 
WHERE LOWER(CONCAT(first_name, ' ', last_name)) LIKE LOWER('%doe%');

That's getting MySQL to do work on each row though.