MySQL fulltext search over multiple columns

Question

I've created a table with an full-text index over 2 columns:

CREATE TABLE players
(
    id int NOT NULL,
    first_name varchar(25) NOT NULL,
    last_name varchar(25) NOT NULL,
    team_id int NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT INDEX full_text_pname (first_name, last_name),
    CONSTRAINT p_team_id FOREIGN KEY (team_id) REFERENCES teams (id)
);

Now I want to do a SQL query that recives first_name and last_name and selects the players with those values.

Instead of:

   SELECT first_name, last_name, team_id
   FROM players
   WHERE first_name = % s AND last_name = % s

How can i use match and against?

Answer 1

The syntax for the MATCH() function goes like this:

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

see documentation

So the query would be something like this:

SELECT first_name, last_name, team_id
FROM players
WHERE MATCH ( first_name ) AGAINST ("my_first_name" IN BOOLEAN MODE) AND
      MATCH ( last_name ) AGAINST ("my_last_name" IN BOOLEAN MODE);

Answer 2

Fastest This

WHERE first_name = '...'
  AND last_name = '...'

with

INDEX(last_name, first_name)

would be faster than using FULLTEXT

Medium speed for doing an equality match on both columns, you need

WHERE MATCH(last, first)  -- the order must match the index
  AGAINST('+James +Rick IN BOOLEAN MODE)

with

FULLEXT(last, first)

And it has various issues. It matches Rick, James as well as James, Rick. Probably it would match Jame, Ricks since it treats words as English and plays with endings. Etc.

Slowest This does not run fast:

WHERE MATCH ...
  AND MATCH ...