MySQL में Indexes

MySQL में Index क्या करता है?

Index ordered access structure है जो MySQL को every table row पढ़े बिना qualifying rows find करने में help करता है। PRIMARY KEY, UNIQUE और ordinary INDEX जैसे most MySQL indexes B-trees use करते हैं। Index equality, ranges, joins, ordering, grouping और कभी covering query support कर सकता है।

Indexes read efficiency के बदले storage और write-maintenance cost लेते हैं। Good design real query patterns से शुरू होता है, every WHERE column index करने के rule से नहीं।

Verified Orders Index Lab

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
) ENGINE = InnoDB;

INSERT INTO orders_index_lab VALUES
(1, 101, 'PAID',      '2026-08-01', 1200.00),
(2, 101, 'PENDING',   '2026-08-05',  500.00),
(3, 102, 'PAID',      '2026-08-03',  750.00),
(4, 103, 'CANCELLED', '2026-08-04',  300.00),
(5, 101, 'PAID',      '2026-08-10', 1500.00),
(6, 102, 'PENDING',   '2026-08-11',  900.00),
(7, 104, 'PAID',      '2026-08-12', 2200.00),
(8, 101, 'PAID',      '2026-08-14',  650.00);

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

Tiny dataset results verify करता है, speed नहीं। Optimizer all eight rows scan कर सकता है क्योंकि वह cheap है। Performance judge करने से पहले representative volume populate करें।

Primary, Secondary, Unique और Specialized Indexes

Every InnoDB table में clustered index होता है। Primary key न हो तो InnoDB suitable UNIQUE NOT NULL index चुनता या hidden clustered row ID बनाता है। Short, stable primary key deliberately define करें क्योंकि उसकी columns secondary index records में carried होती हैं।

Index Create, List, Hide और Drop

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

CREATE INDEX idx_order_date
ON orders_index_lab (order_date);

SHOW INDEX FROM orders_index_lab;

Names leading columns या business access path state करें। CREATE INDEX existing table में index add करता है; primary keys CREATE TABLE या ALTER TABLE से बनती हैं।

-- Secondary index permanently drop किए बिना optimizer behavior test।
ALTER TABLE orders_index_lab
ALTER INDEX idx_order_date INVISIBLE;

ALTER TABLE orders_index_lab
ALTER INDEX idx_order_date VISIBLE;

DROP INDEX idx_order_date ON orders_index_lab;

Invisible secondary index maintained रहती है पर optimizer ignore करता है, जब तक invisible-index use explicitly enabled न हो। Primary key invisible नहीं हो सकती। Index remove करने से पहले constraints, workload, replicas और dependent plans check करें।

Composite Index और Leftmost-Prefix Rule

(customer_id, status, order_date) के usable lookup prefixes customer_id; customer_id + status; और all three columns हैं। ये predicates उस order से align हैं:

-- Prefix 1
WHERE customer_id = 101

-- Prefix 2
WHERE customer_id = 101 AND status = 'PAID'

-- Equality columns के बाद date range
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'

ये same direct lookup prefix नहीं बनाते:

WHERE status = 'PAID';

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

MySQL another index, Index Merge, scan या another optimization use कर सकता है। Column order equality, ranges, sorting, selectivity और complete workload reflect करे—universal “most selective first” slogan नहीं।

Covering query

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

All requested values composite index में हैं। InnoDB secondary records primary-key columns भी रखती हैं, इसलिए order_id select करना often covered रह सकता है। Coverage assume नहीं, chosen plan verify करें।

Index-Friendly, Sargable Predicates लिखें

Column पर function ordinary column index को direct lookup में रोक सकता है। MySQL suitable expressions के लिए functional key parts और generated-column indexes भी support करता है, पर clear range predicate अक्सर simpler है।

• Narrow result sufficient हो तो SELECT * न लें।
• Never filtered, joined, sorted या grouped columns index न करें।
• Most rows same value share करें तो status-alone low-cardinality index weak हो सकती है।
• Query most table rows चाहती हो तो index कम helpful है।
• Range condition बाद के composite key parts के lookup/ordering help को limit कर सकती है।

EXPLAIN और EXPLAIN ANALYZE से Verify

EXPLAIN
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;

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

Index Design और Maintenance Checklist

1. High-cost, high-frequency query patterns collect करें।
2. Equality, range, join और ordering columns together identify करें।
3. Important patterns serve करने वाले fewest composite indexes design करें।
4. InnoDB primary key short और stable रखें।
5. Before/after EXPLAIN और representative row counts test करें।
6. Another index add करने से पहले duplicate/redundant indexes check करें।
7. Write latency और storage growth measure करें क्योंकि relevant indexes maintain होती हैं।
8. Need होने पर ANALYZE TABLE से optimizer statistics refresh करें।
9. Appropriate हो तो reversible optimizer-use test के लिए invisible indexes लें।
10. Workload change पर indexes review करें; yesterday का useful index redundant हो सकता है।

अभ्यास: composite index create करें; three leftmost prefixes test करें; status alone query करें; YEAR function को date range से compare करें; secondary index invisible बनाएँ; large dataset पर EXPLAIN से selected plan record करें।

अगले lessons में primary और secondary indexes, composite indexes और EXPLAIN query plans detail में हैं।

Official संदर्भ

• MySQL 8.4: How MySQL Uses Indexes
• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: EXPLAIN Statement

Index structures, leftmost-prefix behavior और plan verification official MySQL 8.4 manual से check किए गए हैं।