Help understand this mySQL query with ORDER BY and CASE

Question

I am trying to understand this mySQL query. It is assumed that he searches for “elements” of the table with the search term “brown fox lazy dog” and returns a result based on its relevance or the number of times this occurs in the records.

SELECT * FROM `items` WHERE `description` LIKE 'quick' AND ( `description` LIKE 'brown' OR `description` LIKE 'fox' OR `description` LIKE 'lazy' OR `description` LIKE 'dog' ) ORDER BY ( ( CASE WHEN `description` LIKE 'brown' THEN 1 ELSE 0 END ) + ( CASE WHEN `description` LIKE 'fox' THEN 1 ELSE 0 END ) + ( CASE WHEN `description` LIKE 'lazy' THEN 1 ELSE 0 END ) + ( CASE WHEN `description` LIKE 'dog' THEN 1 ELSE 0 END ) ) DESC LIMIT 0, 30 

The part that I don’t understand is in the part in the ORDER BY and CASE clauses. Say that a particular record matches all 4 keywords that we are looking for, do we get ORDER BY (4)? How is this 4 related to a particular row? As far as I understand, ORDER BY is usually used in a column, not in a number? Thank you

Answer 1

ORDER BY can be used for any value obtained from the fields in each row. In this case, we order the number of important words that appear in the description field. So, as you say, if a particular entry matches all 4 keywords, then it gets 4 and ends at the top of the (descending) order. If an entry matches only one of the keywords, it gets 1 and ends at the bottom. You can even say ORDER BY RAND (), which does exactly what you expect. ORDER BY RAND () LIMIT 1 is a very common idiom for choosing one row at random from a table. However, do not use ORDER BY RAND () LIMIT 1 in large tables, because it still generates a random number for each row (very expensive) to figure out which one is the smallest.

Answer 2

the number here refers to the column that will be used to organize the results, the first column will be 1.
I could not find a link for this online, but I found it to be random, and sometimes it comes in handy.

Answer 3

This query is used to sort by items that you can say "relevance" ...

The order in parts will be, for example, if the description looks like "% dog%" order by takes 1, and if the same description contains the "lazy" order, it will be 1 + 1; this means that in the current line there were two words “dog” and “lazy”. etc.

This query organizes the elements using "Description that contains the maximum number of keywords", so if a row entry matches all 4 keywords, then it gets 4 and ends in the top (descending) order. If a row entry matches only one of the keywords, it gets 1 and ends at the bottom.