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

documentationfull-text-searchinnodbMySQL

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?

Best Answer

(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?)

Related Question