Search a string in multiple fields of a table - 【StackMirror】|php|mysql|sql

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

2012-04-04 07:03
by Habeeb Perwad

Is it efficient when compared to what - Jon 2012-04-04 07:05

According to this comment http://stackoverflow.com/questions/9986678/searching-more-than-1-column/9986696#comment12762483_9986696 UNION is faste - Andreas Wong 2012-04-04 07:06

@Jon I want to get a better query to do that task. I am not comparing with anything - Habeeb Perwad 2012-04-04 07:55

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.

2012-04-04 07:42
by Ankit Sharma

As soon as you have a LIKE '%something%' in your WHERE clause, you force a table scan. So yes, it is inefficient, but one or three LIKE statements will make little difference.

The table scan is the big performance hit.

Consider looking at MySQL's Full Text Search capability. It is designed to answer this type of query much more efficiently.

2012-04-04 07:06
by Eric J.

If you need to search for a pattern in more than one fields, and if you have the permission to change table schema, i would suggest implementing FULL-TEXT SEARCH.

Hope it helps :)

2012-04-04 07:09
by web-nomad