จัดลำดับตามความเกี่ยวข้อง (Relevance) ใน MySQL ด้วย CASE WHEN

เมื่อต้องการเรียงผลลัพธ์ตาม "ความเกี่ยวข้อง" (Relevance) เช่น ค้นหาชื่อ "Carol" ให้ "Carol Adams" อยู่ก่อน "Sam Carol Bond" ใช้ CASE WHEN ใน ORDER BY

ตัวอย่าง: ค้นหาชื่อที่มี "Carol"

1. สร้างตารางและเพิ่มข้อมูล

mysql> CREATE TABLE OrderByRelevance (
    -> UserId INT,
    -> UserName VARCHAR(200)
    -> );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO OrderByRelevance VALUES 
    -> (101, 'Carol Smith'),
    -> (102, 'Carol Adams'),
    -> (103, 'Carolnathan Todd'),
    -> (104, 'John Smith'),
    -> (105, 'Sam Carol Bond');

2. Query แบบธรรมดา (ไม่เรียง Relevance)

mysql> SELECT * FROM OrderByRelevance WHERE UserName LIKE '%Carol%';

+--------+------------------+
| UserId | UserName         |
+--------+------------------+
| 101    | Carol Smith      |
| 102    | Carol Adams      |
| 103    | Carolnathan Todd |
| 105    | Sam Carol Bond   |
+--------+------------------+
4 rows in set (0.00 sec)

ลำดับไม่แน่นอน (ขึ้นกับ Index/Insert Order)

3. เรียงตาม Relevance ด้วย CASE WHEN

mysql> SELECT MAX(UserId) AS Id, UserName 
    -> FROM OrderByRelevance 
    -> WHERE UserName LIKE '%Carol%' 
    -> GROUP BY UserName 
    -> ORDER BY 
    ->     CASE 
    ->         WHEN UserName LIKE 'Carol%' THEN 0           -- เริ่มต้นด้วย Carol
    ->         WHEN UserName LIKE '% %Carol% %' THEN 1      -- Carol อยู่กลาง (มี Space รอบๆ)
    ->         WHEN UserName LIKE '%Carol' THEN 2           -- ลงท้ายด้วย Carol
    ->         ELSE 3
    ->     END,
    ->     UserName;

+------+------------------+
| Id   | UserName         |
+------+------------------+
| 102  | Carol Adams      |
| 101  | Carol Smith      |
| 103  | Carolnathan Todd |
| 105  | Sam Carol Bond   |
+------+------------------+
4 rows in set (0.00 sec)

หลักการจัดอันดับ Relevance

1. Exact Match / Prefix: เริ่มต้นด้วยคำค้นหา (Highest)
2. Word Boundary: เป็นคำเต็มตัว (มี Space/Edge รอบๆ)
3. Substring: อยู่ในคำอื่น
4. Suffix: ลงท้ายด้วยคำค้นหา
5. Else: ที่เหลือ

Full-Text Search (MySQL 5.6+ InnoDB) - วิธีมืออาชีพ

ALTER TABLE OrderByRelevance ADD FULLTEXT(UserName);

SELECT UserName, MATCH(UserName) AGAINST('Carol' IN NATURAL LANGUAGE MODE) AS relevance
FROM OrderByRelevance
WHERE MATCH(UserName) AGAINST('Carol' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

Boolean Mode (ควบคุมได้ละเอียด)

SELECT UserName, 
       MATCH(UserName) AGAINST('+Carol*' IN BOOLEAN MODE) AS score
FROM OrderByRelevance
WHERE MATCH(UserName) AGAINST('+Carol*' IN BOOLEAN MODE)
ORDER BY score DESC;

ตัวอย่าง Relevance แบบหลายเงื่อนไข

SELECT *,
    CASE 
        WHEN title LIKE 'search_term%' THEN 100
        WHEN title LIKE '% search_term%' THEN 50
        WHEN title LIKE '%search_term%' THEN 10
        ELSE 0
    END AS title_score,
    CASE 
        WHEN description LIKE '%search_term%' THEN 5
        ELSE 0
    END AS desc_score
FROM articles
WHERE title LIKE '%search_term%' OR description LIKE '%search_term%'
ORDER BY (title_score + desc_score) DESC, created_at DESC;

สรุป