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):

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
);

• Example (create composite index):

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, STATUS, created_at);

• Example (queries that benefit):

-- 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';

• Example (query that may NOT benefit much):

-- Missing leftmost column (user_id)
SELECT *
FROM orders
WHERE STATUS = 'paid'
ORDER BY created_at DESC;

• How to verify (EXPLAIN):

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10 AND STATUS = 'paid'
ORDER BY created_at DESC
LIMIT 50;

• 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)