support@testinganswers.com

Speed Superpowers! How Database Indexes Help Find Data Fast

11. Indexes and Performancetestinganswers.com

Imagine you have a huge storybook with 10,000 pages.

  • If I ask you to find page 7,432,
    would you start reading from page 1?
  • Or would you use the index at the back of the book?

Databases work the same way.
That magical shortcut is called an INDEX.

What Is an Index?

An index is like:

  • A book index
  • A phone contact list
  • A table of contents

It helps MySQL find data faster without checking every row.

Without Index

MySQL says:

“Let me check row 1… row 2… row 3… row 1,000,000…”

With Index

MySQL says:

“Aha! I know exactly where this data lives!”

Real-Life Example

Imagine a school register:

Roll NoName
1Rahul
2Anya
3Rohan

If the teacher wants Rohan:

  • Without index → check every name
  • With index → jump directly to Rohan
Table Example (Our Playground)
CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Searching WITHOUT an Index

SELECT * FROM students WHERE name = 'Rohan';

MySQL scans every row
Slow for big tables
This is called a Full Table Scan

Creating an Index

CREATE INDEX idx_student_name ON students(name);

Boom! Speed unlocked!

Searching WITH an Index

SELECT * FROM students WHERE name = 'Rohan';

Now MySQL:

  • Uses the index
  • Finds the row instantly
  • Skips unnecessary rows

How Indexes Work (Simple Explanation)

Indexes usually use a structure called:

  • B-Tree (Balanced Tree)

Think of it like:

  • A decision tree
  • Every step cuts the search space in half

That’s why indexes are FAST.

Types of Indexes in MySQL

PRIMARY KEY Index

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

✔ Automatically indexed
✔ Unique
✔ Cannot be NULL

UNIQUE Index

CREATE UNIQUE INDEX idx_email ON users(email);

✔ No duplicate values
✔ Great for emails, usernames

NORMAL (NON-UNIQUE) Index

CREATE INDEX idx_city ON students(city);

✔ Duplicates allowed
✔ Improves search speed

COMPOSITE Index (Multiple Columns)

CREATE INDEX idx_city_age ON students(city, age);

Order matters!

  • Works for (city)
  • Works for (city, age)
  • Does NOT work for (age) alone

When NOT to Use Indexes

Indexes are powerful, but not magic

Avoid indexes when:

  • The Table is very small
  • Column changes frequently (UPDATE heavy)
  • The column has very few unique values (e.g., gender)

Indexes speed up SELECT, but slow down INSERT/UPDATE/DELETE

Checking Index Usage

EXPLAIN SELECT * FROM students WHERE name = 'Rohan';

Look for:

  • key: idx_student_name
    If it says NULL → index not used

PRACTICE QUESTIONS

Q. Why are indexes faster than table scans?

Answer:
Indexes allow MySQL to jump directly to the required data instead of checking every row.

Q. Create an index on age column.
CREATE INDEX idx_age ON students(age);
Q. What happens if too many indexes exist?

Answer:

  • Slower inserts and updates
  • More disk usage
  • Maintenance overhead
Q. Does index speed up DELETE?

Answer:
No
Indexes mainly speed up SELECT queries.

INTERVIEW QUESTIONS

1. What is an index in MySQL?

Answer:
An index is a data structure that improves the speed of data retrieval operations on a database table.

2. Does PRIMARY KEY create an index?

Answer:
Yes. MySQL automatically creates a unique index for the PRIMARY KEY.

3. What is a composite index?

Answer:
An index created on multiple columns is useful when queries filter using more than one column.

4. Why do indexes slow down INSERT operations?

Answer:
Because MySQL must update the index every time new data is inserted.

5. How do you check whether an index is used?

Answer:
Using the EXPLAIN statement.