support@testinganswers.com

Speed Superpowers for Databases! Becoming an Index Ninja

11. Indexes and Performance, Testingtestinganswers.com

Imagine a huge library with millions of books.
If the books are not organized, finding one book will take forever.

Now imagine the library has a magic index card system that tells you exactly where each book is.
That magic system is called an INDEX in databases.

In this lesson, we’ll learn about Creating and Managing Indexes in
MySQL
— step by step, slowly, clearly, and with lots of examples.

What Is an Index?

An index is like:

  • A book index (page numbers)
  • A school attendance register
  • A Google search shortcut

It helps MySQL find data faster without checking every row.

Without Index vs With Index

Without IndexWith Index
MySQL checks every rowMySQL jumps directly
Slow queries Fast queries
More CPU usageLess CPU usage

Sample Table (Our Playground)
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    marks INT
);

Why Do We Need Indexes?

Suppose you run this query:

SELECT * FROM students WHERE city = 'Delhi';

If the table has 1 million rows, MySQL will scan all rows

Solution: Create an index on city

Creating Indexes (Step by Step)

1. Create a Simple Index

CREATE INDEX idx_city ON students(city);

Now MySQL can find students from Delhi very fast.

2. Create an index while creating a table

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    department VARCHAR(50),
    INDEX idx_department (department)
);

3. Unique Index (No Duplicates Allowed!)

Think of Aadhaar / Roll Number 🪪

CREATE UNIQUE INDEX idx_unique_email
ON users(email);

Ensures no duplicate emails

4. Composite Index (Index on Multiple Columns)

Like searching for a student by class + section

CREATE INDEX idx_city_marks
ON students(city, marks);

Order matters!

Good:

WHERE city = 'Delhi' AND marks > 80

Not ideal:

WHERE marks > 80

Checking Indexes

SHOW INDEX FROM students;

Shows all indexes on the table

Dropping (Deleting) an Index

Indexes are helpful, but too many = trouble

DROP INDEX idx_city ON students;

Advantages & Disadvantages (Simple Table)

Advantages
  • Faster SELECT queries
  • Better performance
  • Efficient searching
Disadvantages
  • Slower INSERT/UPDATE/DELETE
  • Extra storage space
  • Too many indexes = confusion

Golden Rule:
Index columns used in WHERE, JOIN, ORDER BY, GROUP BY

Real-Life Example
SELECT * FROM orders
WHERE customer_id = 101
ORDER BY order_date;

Best Index:

CREATE INDEX idx_customer_date
ON orders(customer_id, order_date);

Practice Questions (With Answers)

Q1. What is an index?

Answer:
An index is a data structure that helps MySQL find rows faster.

Q2. Create an index on age column.
CREATE INDEX idx_age ON students(age);
Q3. What happens if we create too many indexes?

Answer:

  • Slower data insertion
  • More disk usage
Q4 Which query benefits most from indexes?
SELECT * FROM students WHERE id = 10;

Because id is indexed (PRIMARY KEY)

Q5 How to see all indexes on a table?
SHOW INDEX FROM students;

Interview Questions & Answers (Beginner → Advanced)

Q1. What is an index in MySQL?

Answer:
An index improves query speed by reducing the number of rows MySQL scans.

Q2. Difference between PRIMARY KEY and INDEX?

Answer:

PRIMARY KEYINDEX
Unique + Not NullCan have duplicates
Only one allowedMultiple allowed
Q3. What is a composite index?

Answer:
An index created on multiple columns.

Q4. Does index improve INSERT performance?

Answer:
No. INSERT becomes slower because indexes must be updated.

Q5. When should you avoid indexes?

Answer:

  • Small tables
  • Columns with frequent updates
  • Columns with very few unique values
Indexing Best Practices
  • Index foreign keys
  • Index frequently searched columns
  • Don’t index every column
  • Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM students WHERE city = 'Delhi';