Database - the best way, how to search names

Question

I am solving in these days following situation: In my DB table I have columns for name and surname.

On my page I have input for searching people, and I am struggling with a problem, how to search the name in database that is stored in two columns and I have the name as string ("Joe Green").

For example, in database I have followings:

Joe New
Joe Blue
Joe Green
Joe Francois Green

What could be the best way, how this problem to solve? I am currently working with MySQL database and Rails 3.

EDIT: Thank you guys for you replies, but I don't know, how to make the query in Rails 3 notation, is it possible to use "concat"?

Answer 1

if your database table engine is myISAM then use FULLTEXT search

first create FULLTEXT index by

CREATE FULLTEXT INDEX fx_name ON pages (name, surname)

then use below query to retrieve required data

SELECT  *
FROM    table
WHERE   MATCH(name,surname) AGAINST ('keyword' IN BOOLEAN MODE)

update

alternative:use CONCAT

first create index

CREATE INDEX users_firstandlast ON users(name, surname);

option1: repeat the CONCAT in your WHERE clause (because AS doesn't create a name you can use in the WHERE clause):

SELECT CONCAT(name, ' ', surname) as fullname 
FROM users 
WHERE CONCAT(name, ' ', surname) LIKE '%joe green%';

option 2:

SELECT CONCAT(name, ' ', surname) as fullname 
FROM users 
WHERE name LIKE '%joegreen%' or surname LIKE '%joegreen%';

More information