A composite index on (a, b, c) is usable by a query filtering on a, on a and b, or on all three. It is not usable by a query filtering only on b, or only on c. MySQL reads a composite index left to right and stops at the first column your WHERE clause does not mention.
ALTER TABLE orders ADD INDEX idx_lookup (customer_id, status, created_at);
-- uses the index
SELECT * FROM orders WHERE customer_id = 91;
SELECT * FROM orders WHERE customer_id = 91 AND status = 'paid';
-- cannot use it: no customer_id, so the leftmost column is missing
SELECT * FROM orders WHERE status = 'paid';This is why three separate single-column indexes are not equivalent to one composite index, and why adding a column to the front of an existing index silently breaks queries that were relying on it. Order the columns by how the query filters, not by how the table is laid out — and confirm with EXPLAIN rather than assuming.