I've got a table of first and last names

firstname    lastname
---------    ---------
Joe          Robertson
Sally        Robert
Jim          Green
Sandra       Jordan

I am attempting to search this table according to a port that includes the entire title. For instance:

input: Joe Robert

I figured about using

SELECT * FROM tablename WHERE firstname LIKE

However the table stores the foremost and surname individually, so I am unsure how you can perform the search within this situation


FROM    mytable
WHERE   MATCH(firstname, lastname) AGAINST ("Joe* Robert*" IN BOOLEAN MODE);

This can run considerably faster should you produce a FULLTEXT index:

CREATE FULLTEXT INDEX ON mytable (firstname, lastname)

For that query to have the ability to search rapid names (like Joe inside your situation), you will need to set the variable @@ft_min_word_len to 1 before creating the index.

In InnoDB you will need to split the search string:

FROM    mytable
WHERE   firstname LIKE 'Joe%'
        AND lastname LIKE 'Robert%'

An alternative choice to Quassnoi's method:

FROM    mytable
WHERE   CONCAT(firstname, " ", lastname) = "Joe Robert"