Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

All we need is an easy explanation of the problem, so here it is.

In the official documentation of Boolean Full-Text Searches:

Boolean full-text searches have these characteristics:

  • They do not automatically sort rows in order of decreasing relevance.

However, when running full-text searches IN BOOLEAN MODE (mysql v8.0.23), they are sorted by relevance in order of decreasing relevance without having to use ORDER BY score

SELECT product_category_id,
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE) as score
FROM product
WHERE 
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE);

Result:

+---------------------+--------------------+
| product_category_id | score              |
+---------------------+--------------------+
|                  97 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |

What have I missed here?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

(A guess)

There are many queries where the results are in a predictable order even without an ORDER BY. However, this is often an artifact of the implementation and not a guaranteed ordering.

It used to be that GROUP BY automatically ordered the results as if there were a matching ORDER BY. However, the existence of that as a guarantee hobbled the Optimizer. With an appropriate INDEX, such a group by will continue to be in order. But for other cases, the Optimizer would rather use an in-memory hash table; this essentially guarantees a somewhat random order; furthermore, it makes getting the order be an extra effort.

So, I guess, the manual is leaving the door open for some future optimization.

Rule: If you want a particular order, specify ORDER BY. If the Optimizer sees that the code can (or could) guarantee the order without doing any extra effort, it will do so.

PS, in the past, you could nullify the GROUP BY's implicit ordering by saying ORDER BY NULL. (Kludgy?)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply