How does the column order in a composite index change which queries it can serve?
The order of the columns decides which queries the index can serve, and the rule is that the leftmost columns have to be used.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
placed_on TEXT NOT NULL
);
-- Two columns, in this order.
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
So which queries can it serve? An index on the pair of the customer and the status can be used when the query filters on the customer, when it filters on the customer and the status, and when it filters on all three. It cannot be used when the query filters on the status alone.
the query filters on can the index be used?
------------------------------ ----------------------------
customer_id yes
customer_id and status yes, and it is the best one
status alone no
placed_on alone no
So why is that the rule? Because the index is sorted by the first column, and then by the second inside that. The engine can therefore find the block of rows for one customer, and then look inside that block for one status. But it cannot look for one status across all the customers, because the statuses are scattered through the index.
So the rule to remember is this. Put the column that you filter on most often first, and then the order of the rest of the columns is the order in which they are usually filtered.
The next question in this topic asks when one big index is worse than two small ones.