[Solved] Full Text Search on MySQL, but word order must matter

EverSQL Database Performance Knowledge Base

Full Text Search on MySQL, but word order must matter

Database type:

I'm doing a MySQL search using MATCH/AGAINST IN BOOLEAN MODE, but I need that the word's order matters.

Assuming that I have the following content on the column "names" on table "users":

  +---------------------------------+
  | names                           |
  +---------------------------------+
  | RONSON SIMPSON HOMER JAY        |
  | SIMPSON RONSON HOMER JAY        | 
  | RONSON SIMPSON JAY HOMER        |
  | SIMPSON RONSON JAY HOMER        |
  +---------------------------------+

If I'm looking for SIMPSON HOMER, then I need that the very first result be:

FYI, now I'm using the following SQL query:

SELECT *, MATCH(names) AGAINST('+SIMPSON +HOMER') AS relevance FROM users WHERE MATCH(names) AGAINST('+SIMPSON +HOMER' IN BOOLEAN MODE) HAVING relevance > 0.2 ORDER BY  relevance DESC, names ASC LIMIT 30;

I'm asking because I didn't find a good answer on SOF or the rest of the internet - the most closest topic was this: MySQL order by "best match", but answers aren't working to me.

I also apologize for the bad example.

Please, no %LIKE% suggestions, because performance of the query is important in my environment

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Mixed Order By Directions Prevents Index Use (query line: 11): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
The optimized query:
SELECT
        *,
        MATCH (users.names) AGAINST ('+SIMPSON +HOMER') AS relevance 
    FROM
        users 
    WHERE
        MATCH (users.names) AGAINST ('+SIMPSON +HOMER' IN BOOLEAN MODE) 
    HAVING
        relevance > 0.2 
    ORDER BY
        relevance DESC,
        users.names ASC LIMIT 30

Related Articles



* original question posted on StackOverflow here.