Search a string in multiple fields of a table

Question

I have a table User which has the fields (id, first_name, middle_name, last_name).

I want to write a query to find a user by his name. The name may be first name, middle name or last name.

$sql = "SELECT * FROM user 
        WHERE first_name like '%$name%' OR  
              middle_name like '%$name%' OR
              last_name like '%$name%'";

Is it efficient query? (Leave the security issue for the time being.)

Answer 1

Alter table and add composite Fulltext index on First_name,second_name,last_name then use this query

select * 
from table_name 
where match (`First_name`,`second_name`,`last_name`) against('name')

It's pretty much faster then your query.