🚀 Introduction

Have you ever run a SQL query that just took forever and wondered, "Why is this so slow?" You're not alone. Databases are fast until they're not. And one of the simplest, yet most powerful performance tricks in SQL is indexing.
We ran a real-world experiment on 80,000+ records to see how MySQL indexing affects performance. Here's what happened.
In this post, we'll explore:

  • What indexing really means in MySQL

  • Why indexes are performance game changers

  • The exact results of an experiment we ran on 80,000+ records in

  • MySQL 5.7 on a local setup (8GB RAM, SSD)

  • When indexing helps and when it doesn't

Let's dive in.

Cat meme

🔍 What is an Index in MySQL?

Think of an index like a book's table of contents. If you need Chapter 8, you don't flip through every single page you jump straight to it. That's what MySQL does with an index.
Under the hood, indexes in MySQL are usually implemented using a B-tree, which allows the database to quickly search for a value, similar to how a binary search works.
There are different types of indexes:

  • Primary Index(based on the primary key)
  • Unique Index (for ensuring uniqueness)
  • Composite Index (on multiple columns)
  • Fulltext Index (for searching large text fields)

But theory is one thing. Let's get into what actually happened when we ran an experiment.

🧪 Experiment Setup

We wanted to test just how much indexing can impact performance. So we created a MySQL table with 80,000 rows of dummy data using Python.
🔧 Table Structure:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
age INT,
city VARCHAR(255),
created_at DATETIME
);

No indexes were added except the default PRIMARY KEY on id.
🧑‍💻 Query Used:
`SELECT * FROM users WHERE city = 'Mumbai';

`We ran this query under two scenarios:

  1. Without indexing on city
  2. With indexing on city

To simulate real-world conditions, we repeated each query 10 times and calculated the average execution time using Python's time module and MySQL's EXPLAIN ANALYZE.

📈 Results

Here's what we found:
🔹 Without Index:

  • Average execution time: ~420ms
  • EXPLAIN showed a full table scan (rows examined: ~80,000)

**🔹 With Index on city:
**CREATE INDEX city_index ON users(city);

  • Average execution time: ~8ms
  • EXPLAIN showed an index lookup (rows examined: ~300)

That's 50x faster, just by adding a single index!

💡 Key Takeaways

  • Indexes drastically reduce query execution time when filtering large datasets.
  • EXPLAIN is your best friend for debugging SQL performance.
  • Indexes come with write overhead - they slow down INSERT, UPDATE, and DELETE slightly. So, use them strategically.
  • Always benchmark with your real query pattern and data volume.

🔮 What's Next?

In our next blog, we'll take this one step further:

  • Adding composite indexes
  • Trying indexes on multiple fields
  • Testing against pagination queries like LIMIT OFFSET
  • Comparing performance gains with even larger datasets

Stay tuned for Part 2 of our indexing deep-dive!
Have you ever optimized a query using indexing? What performance gain did you notice?
Share your thoughts or questions in the comments.👇