0

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:

  • SIMPSON RONSON HOMER JAY

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

eplazai
  • 306
  • 2
  • 9

1 Answers1

0
... AGAINST('+"SIMPSON HOMER"' IN BOOLEAN MODE)

A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed.

https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

Note also that using a fulltext MATCH ... AGAINST and LIKE in the same query results in the optimizer using the fulltext index first, then filtering only the matched rows using LIKE, so it can be quite valid and perform well... though it should not be needed, here.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • Excuse me, sir. How can I mix MATCH/AGAINST and LIKE? (in the context of my question). I've been trying without luck. Thank You! – eplazai Jun 19 '18 at 06:13
  • 1
    @eplazai - `WHERE (..LIKE..) AND (MATCH...)` will do the `MATCH` first, then further filter with the `LIKE` (or `RLIKE`). – Rick James Jun 20 '18 at 14:13