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);
+---------------------+--------------------+ | 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.
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 🙂