Question 21316 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL fulltext search over multiple columns

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?

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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 ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search