Composite Indexes
Composite, (multi-column), index, MULTIPLE, columns, ko, EK, saath, index, karta hai — column, ORDER, matter, karta hai. "LEFTMOST, PREFIX" rule, ke, according, index, {a,b,c}, queries, ko, support, karta hai, jo, {a}, {a,b}, ya, {a,b,c} par, filter, karti, hain.
MOST, SELECTIVE, (SABSE, ZYAADA, unique, values, waala) column, ko, PEHLE, RAKHNA, GENERALLY, EK, GOOD, practice, hai, LEKIN, ACTUAL, query, patterns, ke, HISAAB, se, DECIDE, karna, CHAHIYE.
CREATE INDEX idx_status_date ON orders(status, created_at);
-- Ye query, index, USE, karti hai:
SELECT * FROM orders WHERE status = 'completed' ORDER BY created_at DESC;
-- Ye query, index, EFFICIENTLY, use, NAHI, karegi:
-- SELECT * FROM orders WHERE created_at > '2024-01-01';- Composite index = multiple columns, ek, saath — column, ORDER, matter, karta, hai
- "Leftmost prefix" rule — index, ke, leftmost, fields, ke, queries, hi, use, kar, sakte, hain
- ESR (Equality-Sort-Range) rule, se, field, order, decide, hota, hai
Cardinality, EK, column, mein, UNIQUE, values, ki, count, ko, refer, karta hai — HIGH, cardinality, (jaise, email) columns, LOW, cardinality, (jaise, boolean, status) columns, se, ZYAADA, EFFECTIVE, indexes, banaте, hain.
Agar, EK, query, ke, saare, fields, (WHERE, ORDER BY, SELECT) EK, composite, index, mein, HI, present, HAIN, MySQL, ko, actual, row, fetch, karne, ki, ZAROORAT, nahi, — "covering, index" query, extremely, FAST, hoti, hai.
CREATE INDEX idx_covering ON orders(status, created_at, amount);
-- Covering query — sirf, index, se, hi, answer, mil, jaata, hai:
SELECT amount FROM orders WHERE status = 'completed' ORDER BY created_at;