MySQL में Composite Index

Composite Index Meaning और Verified Lab

Composite index या multiple-column index two or more columns को one ordered key में store करती है। (customer_id, status, order_date) index पहले customer_id, each customer के भीतर status, फिर each customer-status group में order_date से sorted होती है।

DROP TABLE IF EXISTS orders_index_lab;
CREATE TABLE orders_index_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100)
) ENGINE = InnoDB;

INSERT INTO orders_index_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

CREATE INDEX idx_customer_status_date
ON orders_index_lab (customer_id, status, order_date);

Eight rows output verify कराती हैं, performance नहीं। Plans compare करने के लिए representative volume और distribution use करें।

Leftmost-Prefix Rule

(customer_id, status, order_date) के natural lookup prefixes:

1. (customer_id)
2. (customer_id, status)
3. (customer_id, status, order_date)

“नहीं” का अर्थ index touch करना forbidden नहीं। MySQL full index scan, applicable skip scan, other indexes के साथ Index Merge या another plan choose कर सकता है। Precise rule: condition ordinary direct lookup के लिए leftmost prefix नहीं बनाती।

Composite-Index Column Order कैसे चुनें?

Isolated columns नहीं, important query pattern से शुरू करें:

SELECT order_id, order_date, total_amount
FROM orders_index_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
ORDER BY order_date;

Index order equality conditions customer_id/status पहले और range/ordering column order_date बाद में रखता है। यह pattern के लिए strong candidate है। फिर complete workload check करें:

• Predicates equality, IN या ranges?
• कौन-सी columns joins करती हैं?
• ORDER BY/GROUP BY compatible key order follow करता है?
• Real data पर combined prefix की selectivity?
• Return columns और coverage की value?
• कौन existing indexes redundant होंगी?

Equality, Range और First Gap

Leading equalities से MySQL narrow contiguous section navigate कर सकता है। Next key part range उस section को bound करती है:

WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'

Missing leading part gap बनाती है। Later conditions rows filter कर सकती हैं, missing direct-lookup prefix repair नहीं। Range भी commonly बाद की key parts को same interval narrow करने में limit करती है:

CREATE INDEX idx_customer_date_status
ON orders_index_lab (customer_id, order_date, status);

WHERE customer_id = 101
  AND order_date >= '2026-08-01'
  AND status = 'PAID';

यहाँ customer_id equality और order_date range है। status filter कर सकता है पर before-range equality की तरह interval narrow usually नहीं करता। इसलिए order important है। JSON/TREE में used_key_parts और ANALYZE में actual rows inspect करें।

ORDER BY, Direction और Covering Index

SELECT order_id, status, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

First two parts fixed हैं और next order_date है, इसलिए index separate sort के बिना order produce करने की candidate है। Full plan decide करेगा। Mixed directions और joins eligibility बदल सकते हैं; MySQL query pattern match करने पर descending key parts support करता है।

CREATE INDEX idx_customer_status_date_amount
ON orders_index_lab
  (customer_id, status, order_date, total_amount);

Wider index every requested secondary value रखती है; InnoDB secondary records order_id primary key भी रखती हैं। यह covering हो सकती है। Coverage clustered lookups घटाती है, लेकिन width storage और write maintenance बढ़ाती है। हर query के लिए giant covering index न बनाएँ।

Composite vs Separate Indexes

CREATE INDEX idx_customer ON orders_index_lab (customer_id);
CREATE INDEX idx_status   ON orders_index_lab (status);

CREATE INDEX idx_customer_status
ON orders_index_lab (customer_id, status);

customer_id = ? AND status = ? में composite combined key range directly fetch कर सकती है। Separate indexes में optimizer one index choose करके other predicate filter या Index Merge कर सकता है। Paired pattern में composite often better, पर status-only reports को idx_status चाहिए हो सकती है।

(customer_id, status) simple lookup के लिए separate (customer_id) को generally redundant बनाती है क्योंकि customer_id leftmost prefix है। Width, uniqueness, constraints और workload exceptions हो सकते हैं। Guess नहीं; invisible-index test या controlled drop plan लें।

ALTER TABLE orders_index_lab
ALTER INDEX idx_customer INVISIBLE;

-- Important plans और metrics recheck।

ALTER TABLE orders_index_lab
ALTER INDEX idx_customer VISIBLE;

EXPLAIN से Key Parts Verify

SHOW INDEX FROM orders_index_lab;

EXPLAIN FORMAT=JSON
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01';

EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID';

Composite-Index Design Checklist

1. High-frequency, high-cost query shapes लिखें।
2. Per query equality, join, range और order requirements group करें।
3. Several important patterns serve करने वाला narrow key order चुनें।
4. Leftmost-prefix rule और every gap identify करें।
5. Leading equalities के बाद ORDER BY compatibility check करें।
6. Coverage columns तभी add करें जब measured benefit width justify करे।
7. Duplicate और prefix-redundant indexes check करें।
8. Read gain plus writes/storage cost measure करें।
9. Representative data पर estimates को actual rows से compare करें।
10. Workload/data distribution बदलने पर review करें।

आगे primary-secondary indexes, EXPLAIN plans और query optimization पढ़ें।

Official संदर्भ

• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: ORDER BY Optimization
• MySQL 8.4: EXPLAIN Output Format

Leftmost-prefix behavior, plan fields, covering signals और ordering trade-offs official MySQL 8.4 manual से verify किए गए हैं।