Primary Index और Secondary Index

Primary vs Secondary Index: मुख्य अंतर

InnoDB table में clustered index वह B-tree है जिसकी leaf records complete row data रखती हैं। Declared PRIMARY KEY वही clustered index बनती है। हर अन्य index secondary index है: उसकी leaf records secondary key और row की primary-key columns रखती हैं।

Verified Orders 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,
  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');

SELECT order_id, status, total_amount
FROM orders_index_lab
WHERE order_id = 5;

Result deterministic है। Tiny table meaning दिखाती है, speed नहीं: cost-based optimizer eight rows पर index मौजूद होने के बाद भी scan choose कर सकता है।

InnoDB Clustered Index कैसे चुनता है?

1. Table में PRIMARY KEY हो तो InnoDB उसे clustered index बनाता है।
2. न हो तो first UNIQUE index चुनता है जिसकी all key columns NOT NULL हों।
3. दोनों न हों तो 6-byte synthetic row ID पर hidden GEN_CLUST_INDEX बनाता है।

इसलिए every InnoDB table में clustered index होता है, भले DDL में visible primary key न हो। फिर भी application को normally meaningful, stable row identifier define करना चाहिए: hidden row ID application SQL से reference नहीं होती और deliberate identity contract नहीं देती।

CREATE TABLE students (
  student_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admission_no VARCHAR(20) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

Auto-increment surrogate एक valid design है, universal requirement नहीं। Genuinely short, immutable और authoritative natural key भी suitable हो सकती है।

Two-Step Secondary-Index Lookup

CREATE INDEX idx_customer
ON orders_index_lab (customer_id);

SELECT order_id, order_date, total_amount, notes
FROM orders_index_lab
WHERE customer_id = 101
ORDER BY order_id;

Conceptually InnoDB idx_customer search करता, matching primary-key values 1, 2, 5, 8 पढ़ता और उनसे full clustered records तक जाता है। इसे bookmark lookup, row lookup या double read भी कहते हैं।

यह कहना गलत है कि secondary leaf independent physical row address रखती है। InnoDB में वह primary-key columns रखती है। इससे page split या row movement के बाद reference logically valid रहता है, लेकिन primary-key width important बनती है।

Secondary Index Query को कब Cover करता है?

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

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

यह query cover हो सकती है: customer_id, status और order_date explicit index parts हैं तथा order_id हर InnoDB secondary record में stored primary key है। SELECT में notes जोड़ने पर index all requested values नहीं रखता और full clustered rows चाहिए।

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

Traditional EXPLAIN में Extra: Using index बताता है कि required column information index tree से मिली। इसका अर्थ केवल “index chosen” नहीं; वह अलग question key field बताती है।

Good InnoDB Primary Key Design

Primary key केवल “natural” होने के कारण न चुनें और surrogate automatically भी न चुनें। Business stability, width, privacy exposure, distributed ID generation, insert locality और referencing tables compare करें।

• INT/BIGINT AUTO_INCREMENT: compact और insertion-friendly; generation coordinate करें और value को business fact न मानें।
• UUID as text: systems में easy पर wide; binary/time-ordered form storage और locality penalty reduce कर सकती है।
• Composite natural key: true identity enforce कर सकती है, पर full width हर secondary record और foreign key में repeat होती है।

Compact surrogate clustered primary key रखते हुए UNIQUE secondary index business identifier enforce कर सकती है।

Definitions Inspect और Plan Verify करें

SHOW CREATE TABLE orders_index_lab;
SHOW INDEX FROM orders_index_lab;

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

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

• SHOW INDEX name, uniqueness, key-part sequence और estimated cardinality confirm करता है।
• EXPLAIN normal SELECT result run किए बिना estimated plan दिखाता है।
• EXPLAIN ANALYZE SELECT run करके TREE में actual timing, rows और loops देता है।

Common Mistakes और Faculty Checklist

• Storage engine बताए बिना every ordinary index को “non-clustered” कहना।
• Physical row order को guaranteed SELECT order समझना; SQL output के लिए explicit ORDER BY चाहिए।
• Secondary-index cost count किए बिना long mutable business string primary key बनाना।
• Composite workload देखने की जगह every predicate के लिए single-column index बनाना।
• Chosen index को clustered lookup elimination मानना; covering plan verify करें।
• Representative production-like data की जगह eight-row lab पर speed judge करना।
• INSERT, UPDATE, DELETE, memory और storage overhead ignore करना।

अभ्यास: lab चलाएँ; primary-key और customer lookup compare करें; SELECT list से notes add/remove करें; Extra inspect करें; फिर representative volume पर repeat करें। आगे composite index design और EXPLAIN query plan पढ़ें।

Official संदर्भ

• MySQL 8.4: Clustered and Secondary Indexes
• MySQL 8.4: Primary Key Optimization
• MySQL 8.4: EXPLAIN Output Format

Cluster selection, secondary-record contents, primary-key width और covering-plan behavior official MySQL 8.4 manual से verify किए गए हैं।