Blog · MySQL / SQL · 04 Jul 2026 · Hindi + English

Index in MySQL: What It Is, How It Works, When It Hurts

An index works like a book's index page — jump straight to the row instead of reading the whole table. CREATE INDEX syntax, proof with EXPLAIN, and the write-cost tradeoff.

The book-index analogy (it is literally the same thing)

You want "Photosynthesis" in a 500-page biology book. Without an index page, you flip through all 500 pages one by one. With the index page at the back — "Photosynthesis ... page 214" — you jump straight there. A database index is exactly this: a sorted lookup structure that lets MySQL jump to matching rows instead of reading the entire table. Reading the whole table is called a full table scan — the 500-page flip.

Creating an index

-- Our situation: 1,00,000 students, we constantly search by name
SELECT * FROM students WHERE name = 'Aman Kumar';
-- Without index: MySQL checks all 1,00,000 rows. Every. Single. Time.

CREATE INDEX idx_name ON students(name);
-- Now MySQL keeps names sorted in a side structure (a B-tree)
-- and finds 'Aman Kumar' in a handful of jumps.

Note: some indexes you already have without knowing — PRIMARY KEY and UNIQUE columns are automatically indexed. That is why searching by roll_no was always fast.

Proof with EXPLAIN — see MySQL's plan

EXPLAIN SELECT * FROM students WHERE name = 'Aman Kumar';

-- BEFORE index:
-- type: ALL        rows: 100000     <- full table scan, all rows!

-- AFTER  CREATE INDEX idx_name:
-- type: ref        rows: 1          <- jumped straight to the row
-- key : idx_name

EXPLAIN is your X-ray machine: put it before any SELECT and MySQL shows whether it will scan everything (type: ALL — bad on big tables) or use an index (ref/range — good).

The price you pay — indexes are not free

If indexes only helped, we would index every column. We don't, because every INSERT, UPDATE and DELETE must also update every index on that table. Like a librarian who must correct the index page every time a book moves. Ten indexes on a busy table = ten extra writes per change = slow inserts. Indexes also consume disk space.

	Reads (SELECT)	Writes (INSERT/UPDATE/DELETE)
With index	✅ Much faster	❌ Slower (index maintenance)
Without index	❌ Full scan	✅ Faster

Which columns deserve an index?

Index these: columns in frequent WHERE (name, email, mobile), JOIN columns (foreign keys like roll_no in fees), columns in ORDER BY of heavy queries.
Don't index: columns rarely searched; tiny tables (scan is already instant); columns with very few distinct values (a gender column with 2 values — the index barely narrows anything).
Composite index tip: CREATE INDEX idx_cs ON students(class, section) serves queries filtering by class alone or class+section — but NOT section alone (leftmost-prefix rule, a favourite advanced question).
Interview line: "An index is a sorted B-tree lookup that trades slower writes and extra storage for dramatically faster reads on searched columns."

किताब के index वाली analogy (यह literally वही चीज़ है)

500 pages की biology किताब में आपको "Photosynthesis" चाहिए. Index page के बिना सारे 500 pages एक-एक करके पलटेंगे. पीछे के index page से — "Photosynthesis ... page 214" — सीधे वहीं पहुंचेंगे. Database index बिल्कुल यही है: एक sorted lookup structure जिससे MySQL पूरी table पढ़ने की बजाय matching rows पर सीधे कूदता है. पूरी table पढ़ना full table scan कहलाता है — वही 500-page वाली पलटाई.

Index बनाना

-- हमारी situation: 1,00,000 students, बार-बार name से search
SELECT * FROM students WHERE name = 'Aman Kumar';
-- बिना index: MySQL सारी 1,00,000 rows check करता है. हर. एक. बार.

CREATE INDEX idx_name ON students(name);
-- अब MySQL names को एक side structure (B-tree) में sorted रखता है
-- और 'Aman Kumar' को गिनती के jumps में ढूंढ लेता है.

Note: कुछ indexes आपके पास बिना जाने पहले से हैं — PRIMARY KEY और UNIQUE columns automatically indexed होते हैं. इसीलिए roll_no से search हमेशा तेज़ थी.

EXPLAIN से proof — MySQL का plan देखिए

EXPLAIN SELECT * FROM students WHERE name = 'Aman Kumar';

-- Index se PEHLE:
-- type: ALL        rows: 100000     <- full table scan, सारी rows!

-- CREATE INDEX idx_name ke BAAD:
-- type: ref        rows: 1          <- सीधे row पर पहुंचा
-- key : idx_name

EXPLAIN आपकी X-ray machine है: किसी भी SELECT के आगे लगाइए और MySQL बताता है कि वह सब scan करेगा (type: ALL — बड़ी tables पर बुरा) या index use करेगा (ref/range — अच्छा).

कीमत भी है — indexes मुफ्त नहीं

अगर indexes से सिर्फ फायदा होता, तो हर column index कर देते. नहीं करते, क्योंकि हर INSERT, UPDATE और DELETE को उस table का हर index भी update करना पड़ता है. जैसे librarian को हर किताब हिलने पर index page ठीक करना पड़े. Busy table पर दस indexes = हर change पर दस extra writes = धीमे inserts. Indexes disk space भी खाते हैं.

	Reads (SELECT)	Writes (INSERT/UPDATE/DELETE)
Index के साथ	✅ बहुत तेज़	❌ धीमे (index maintenance)
Index के बिना	❌ Full scan	✅ तेज़

किन columns पर index बनता है?

इन पर बनाएं: बार-बार WHERE में आने वाले columns (name, email, mobile), JOIN columns (fees में roll_no जैसी foreign keys), heavy queries के ORDER BY columns.
इन पर नहीं: कम search होने वाले columns; छोटी tables (scan वैसे ही instant है); बहुत कम distinct values वाले columns (2 values वाला gender column — index से कुछ खास घटता नहीं).
Composite index tip: CREATE INDEX idx_cs ON students(class, section) सिर्फ class या class+section वाली queries को serve करता है — अकेले section को NAHI (leftmost-prefix rule, favourite advanced question).
Interview line: "Index एक sorted B-tree lookup है जो थोड़े धीमे writes और extra storage के बदले searched columns पर reads को dramatically तेज़ करता है."

Frequently Asked Questions

What is an index in MySQL in simple words?

An index is a sorted lookup structure (a B-tree) on chosen columns, letting MySQL jump directly to matching rows instead of scanning the entire table — exactly like a book's index page.

What is the disadvantage of indexes?

Every INSERT, UPDATE and DELETE must also maintain every index on the table, slowing writes; indexes also consume additional disk space.

How can I check whether MySQL is using my index?

Prefix the query with EXPLAIN — type ALL means a full table scan, while ref or range with your index name in the key column means the index is being used.