An index on multiple columns (a composite or compound index) is a single sorted structure ordered first by its first column, then by its second column within each value of the first, and so on — conceptually like a phone book sorted by last name, then first name within each last name. That ordering is exactly why it's only efficient when a query filters starting from its leftmost column:
CREATE INDEX idx_name ON Employees (last_name, first_name);
-- Uses the index efficiently — filters from the leftmost column
SELECT * FROM Employees WHERE last_name = 'Smith';
SELECT * FROM Employees WHERE last_name = 'Smith' AND first_name = 'Jane';
-- Generally CANNOT use this index efficiently — skips the leftmost column
SELECT * FROM Employees WHERE first_name = 'Jane';
Think of it as the phone-book analogy made literal: knowing someone's first name is "Jane" doesn't help you narrow down a phone book sorted by last name — Janes are scattered throughout, under every last name. The index simply isn't sorted in a way that groups them together.
This is a common real-world mistake: adding a composite index expecting it to speed up queries filtering on either column, when it really only helps queries anchored on the first one (or on both, in leftmost-first order). If both columns genuinely need independent fast lookups, that calls for two separate single-column indexes, not one composite index — most modern optimizers can even combine two single-column indexes for a query that filters on both, though usually less efficiently than one well-chosen composite index would.
When a composite index is genuinely justified (queries reliably filter on both columns together), the usual guidance is to put the more selective column — the one that narrows the result set the most — first, so the engine discards the largest fraction of rows as early as possible. The exception is when queries sometimes filter on only one of the columns; in that case, put whichever column needs to support filtering on its own first, even if it's less selective, since a composite index can't be used leftmost-first if the leftmost column is missing from the query at all.