Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Composite Index and the "Leftmost Prefix Rule":
In Simple Words:
An index on (A, B, C) only accelerates queries that filter on the leftmost column A:
WHERE A = 1 AND B = 2-> Uses index.WHERE B = 2 AND C = 3-> Cannot use index because leading columnAis omitted.
sql
CREATE INDEX idx_users_country_city_status ON users (country, city, status);
-- Uses index (country is leading column):
SELECT * FROM users WHERE country = 'US' AND city = 'Austin';
-- CANNOT use index effectively (leading column missing):
SELECT * FROM users WHERE city = 'Austin';