support@testinganswers.com

Index Superpowers! Making Slow Queries Run Like Lightning

11. Indexes and Performancetestinganswers.com

Imagine your database is a huge school library.

  • Tables = shelves
  • Rows = books
  • Queries = asking the librarian for a book

Now here’s the problem
If the librarian checks every single book, it’s slow
But if the librarian uses a catalog (index) → FAST!

That’s exactly what indexes do in databases like MySQL.

What Is an Index?

Index = Book’s Table of Contents

Instead of reading the whole book:

  • Go to the page number
  • Jump straight to the answer

In databases:

  • Index stores sorted data + pointers
  • Helps MySQL jump directly to matching rows

Query Without Index (Slow Way)

SELECT * FROM students WHERE age = 10;

Database checks every student one by one
This is called Full Table Scan

Query With Index (Fast Way)

CREATE INDEX idx_students_age ON students(age);

Now run the same query:

SELECT * FROM students WHERE age = 10;

MySQL uses the index
Jumps directly to age = 10
Super fast!

How Indexes Work

Think of an index like this:

Age Index
---------
5   → Row 2
7   → Row 9
10  → Row 15
12  → Row 22

MySQL:

  1. Looks in the index
  2. Finds row numbers
  3. Fetches data instantly

Types of Indexes in MySQL

1. PRIMARY KEY Index
  • One per table
  • Unique + NOT NULL
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
2. UNIQUE Index
  • No duplicate values
CREATE UNIQUE INDEX idx_email ON users(email);

Like: Only one child can have one roll number

3. NORMAL (NON-UNIQUE) Index
  • Fast searching
  • Duplicates allowed
CREATE INDEX idx_city ON customers(city);
4. COMPOSITE Index (Multi-column)
CREATE INDEX idx_name_age ON students(name, age);

Order matters!

  • Works for (name)
  • Works for (name, age)
  • Not for (age) alone
5. FULLTEXT Index (Text Search)
CREATE FULLTEXT INDEX idx_content ON articles(content);

Used for:

  • Blogs
  • Search engines

How Indexes Optimize Queries

Example Table
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);

Slow Query

SELECT * FROM orders WHERE customer_id = 101;

Optimized Query

CREATE INDEX idx_customer ON orders(customer_id);

SELECT * FROM orders WHERE customer_id = 101;

Query time drops drastically!

Checking If Index Is Used (EXPLAIN)
EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

Look for:

  • key: idx_customer
  • type: ref or const

When NOT to Use Indexes

Indexes are powerful—but not magic

Avoid indexes when:

  • Table is very small
  • Column changes frequently (INSERT/UPDATE heavy)
  • Low-cardinality columns (like gender)

Indexes:

  • Speed up SELECT
  • Slow down INSERT / UPDATE / DELETE

Balance is key!

Practice Questions (With Solutions)

Practice 1

Question:
Create an index to optimize searching for products by price.

Solution:

CREATE INDEX idx_price ON products(price);
Practice 2

Question:
Which query benefits from a composite index (name, age)?

Answer:

SELECT * FROM students WHERE name = 'Tom' AND age = 10;
Practice 3

Question:
Why is this index not used?

CREATE INDEX idx_age ON students(age);
SELECT * FROM students WHERE age + 1 = 11;

Answer:
Index not used because:

  • Column is modified (age + 1)
  • MySQL cannot use index on expressions

Interview Questions & Answers

Q1: What is an index in MySQL?

Answer:
An index is a data structure that improves query speed by allowing MySQL to locate rows quickly without scanning the entire table.

Q2: What happens if you add too many indexes?

Answer:

  • SELECT becomes faster
  • INSERT/UPDATE/DELETE becomes slower
  • More disk space used
Q3: Difference between PRIMARY KEY and UNIQUE index?

Answer:

PRIMARY KEYUNIQUE
Only one per tableMultiple allowed
Cannot be NULLCan have NULL
Automatically indexedManually created
Q4: What is a covering index?

Answer:
An index that contains all columns required by the query—so MySQL doesn’t need to read the table at all.

Q5: How do you check existing indexes?

Answer:

SHOW INDEX FROM table_name;