====== mysql:index:composite ======
  * **What it is:** A composite index is an index that contains multiple columns in a specific order, e.g. (user_id, status, created_at).
  * **What it’s for:** Speed up queries that filter/sort by those columns, especially when the query uses the leftmost columns first.
  * **Key rule (leftmost prefix):**
    * If your index is (A, B, C), it helps queries using:
      * (A)
      * (A, B)
      * (A, B, C)
    * It usually does **NOT** help as much if you only filter by (B) or (C) without (A).

  * **Example (table):**
<code sql>
CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
</code>

  * **Example (create composite index):**
<code sql>
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);
</code>

  * **Example (queries that benefit):**
<code sql>
-- Uses (user_id, status, created_at)
SELECT *
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

-- Uses leftmost prefix (user_id)
SELECT *
FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 50;

-- Uses (user_id, status) even without ordering
SELECT COUNT(*)
FROM orders
WHERE user_id = 10 AND status = 'paid';
</code>

  * **Example (query that may NOT benefit much):**
<code sql>
-- Missing leftmost column (user_id)
SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC;
</code>

  * **How to verify (EXPLAIN):**
<code sql>
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
</code>

  * **Common mistakes:**
    * Putting low-selectivity columns first (e.g. status) can reduce usefulness.
    * Creating too many indexes slows down INSERT/UPDATE/DELETE.

  * **Vocabulary (hard words):**
    * **composite** /kəmˈpɑː.zɪt/ — composite (kết hợp)
    * **selectivity** /sɪˌlek.tɪˈvɪt.i/ — selectivity (độ phân biệt / mức “lọc” tốt)
    * **prefix** /ˈpriː.fɪks/ — prefix (tiền tố)
    * **verify** /ˈver.ɪ.faɪ/ — verify (xác minh)
    * **slow down** /sloʊ daʊn/ — slow down (làm chậm)