When SQL Indexes Help and When They Hurt Performance

Indexes are frequently presented as an unambiguous performance win, but that's an oversimplification. Every index carries a genuine cost, and in specific, well-understood scenarios, adding an index can actually make a system slower overall rather than faster. Understanding both sides of this tradeoff is what separates genuinely effective performance tuning from indiscriminately indexing every column.

Key Definitions

• Cardinality: The number of distinct values a column contains relative to its total row count; a high-cardinality column (like email) has mostly unique values, while a low-cardinality column (like a boolean flag) has very few distinct values.
• Write overhead: The additional cost incurred by INSERT, UPDATE, and DELETE operations because every relevant index must also be updated to reflect the data change.

What You'll Learn

• Identify the scenarios where an index provides clear, substantial performance benefits.
• Understand the write-performance cost every index introduces.
• Recognize low-cardinality columns as poor index candidates.
• Apply a balanced, deliberate approach to indexing decisions.

Detailed Explanation

Indexes clearly help in well-understood scenarios: columns frequently used in WHERE clauses for equality or range filtering (like doctor_id or appointment_date), columns used in JOIN conditions (foreign keys like doctor_id in appointments), and columns used in ORDER BY, since a B-Tree index's sorted structure can satisfy a sort order directly without a separate, expensive sorting step.

But indexes genuinely hurt performance in specific circumstances. First, write-heavy tables: every INSERT, UPDATE, or DELETE must update every index on the affected columns, so a table with ten indexes pays that maintenance cost ten times over on every single write — for tables experiencing extremely high write volume relative to reads, excessive indexing can become a genuine bottleneck. Second, low-cardinality columns: an index on a column like status, which might only have two or three distinct values ('Completed', 'Cancelled', 'Pending') across millions of rows, provides little to no benefit, since the index can't meaningfully narrow down the search — MySQL's query optimizer may even choose to ignore such an index entirely and perform a full table scan anyway, since scanning might genuinely be faster than following an index that doesn't discriminate between rows effectively. Third, small tables: for a table with only a few hundred rows, a full table scan is often already extremely fast, and the overhead of maintaining an index provides negligible read benefit while still costing something on every write.

The practical takeaway is that indexing should be a deliberate, evidence-based decision — driven by actual query patterns observed with EXPLAIN and real performance measurements — rather than an assumption that 'more indexes is always better.' A genuinely well-tuned schema has indexes precisely where they're needed and nowhere else.

Visual Summary

A balanced scale diagram. Left pan, labeled 'Indexes Help': icons for 'High-cardinality WHERE columns', 'JOIN foreign keys', 'ORDER BY columns'. Right pan, labeled 'Indexes Hurt': icons for 'Write-heavy tables (many indexes = many updates per write)', 'Low-cardinality columns (few distinct values)', 'Very small tables (full scan already fast)'.

Quick Reference

Scenario	Index Recommendation
High-cardinality column used frequently in WHERE/JOIN	Index strongly recommended
Column used in ORDER BY on large result sets	Index recommended
Low-cardinality column (e.g. a boolean or status with 2-3 values)	Index rarely helps, may be ignored by optimizer
Very small table (a few hundred rows)	Index provides minimal benefit
Table with extremely high write volume	Minimize indexes to reduce write overhead

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Good index candidate: doctor_id is high-cardinality and used in JOINs/WHERE constantly
CREATE INDEX idx_appointments_doctor ON appointments(doctor_id);

-- Poor index candidate: status has very low cardinality (just a few distinct values)
-- CREATE INDEX idx_appointments_status ON appointments(status);  -- often provides little benefit

-- Use EXPLAIN to check whether MySQL's optimizer actually chooses to use a given index
EXPLAIN SELECT * FROM appointments WHERE status = 'Completed';
-- MySQL may show 'type: ALL' (full scan) even with an index present,
-- if it estimates the index wouldn't meaningfully narrow down the search.

idx_appointments_doctor is a strong index candidate, since doctor_id has relatively high cardinality (many distinct values relative to row count) and is frequently used in both JOIN conditions and WHERE filters. The commented-out status index illustrates a poor candidate: with only a handful of possible values like 'Completed' or 'Cancelled', an index here often doesn't meaningfully narrow the search space, and MySQL's optimizer may reasonably choose to ignore it in favor of a full table scan, since following the index for a low-selectivity column can actually be slower than simply scanning.

Real-World Examples

• High-write logging and event-tracking systems deliberately minimize indexing on their primary write tables, since write throughput is the critical performance metric, not read speed.
• Database performance consultants routinely find and recommend removing indexes on low-cardinality boolean or status columns that were added without considering cardinality, providing no real benefit.
• E-commerce platforms carefully balance indexing on high-traffic product and order tables, since both fast reads (product search) and fast writes (order placement) matter critically to the business.

Common Mistakes to Avoid

• Assuming more indexes always improve performance, without considering the write overhead or cardinality tradeoffs.
• Indexing low-cardinality columns like boolean flags or status fields, expecting a benefit that rarely materializes.
• Not measuring actual query performance with EXPLAIN before and after adding an index, relying on assumption rather than evidence.

Interview Questions

Q1. In what scenarios does adding an index clearly help query performance?

Indexes help most on high-cardinality columns frequently used in WHERE clause filtering, JOIN conditions (like foreign keys), and ORDER BY sorting, especially on large tables where a full table scan would otherwise be costly.

Q2. Why can an index on a low-cardinality column fail to improve, or even hurt, performance?

A low-cardinality column, like a status field with only a few possible values, doesn't let an index meaningfully narrow down the search space, since a large fraction of rows share the same value. MySQL's optimizer may reasonably choose to ignore such an index and perform a full table scan instead, since navigating the index provides little advantage.

Q3. How does write volume affect indexing decisions?

Every index must be updated on every relevant INSERT, UPDATE, or DELETE, so tables experiencing very high write volume relative to reads should be indexed more conservatively, since excessive indexes can meaningfully slow down write throughput.

Practice MCQs

1. Which type of column is generally a poor candidate for indexing?

1. A high-cardinality column like email
2. A foreign key used in JOINs
3. A low-cardinality column with only a few distinct values
4. A column frequently used in ORDER BY

Answer: C. A low-cardinality column with only a few distinct values

Explanation: Low-cardinality columns don't allow an index to meaningfully narrow down the search space, often providing little benefit and sometimes being ignored by the query optimizer entirely.

2. Why can excessive indexing hurt performance on a write-heavy table?

1. Indexes make SELECT queries slower
2. Every index must be updated on every INSERT, UPDATE, or DELETE, adding cumulative write overhead
3. MySQL only allows a maximum of two indexes per table
4. Indexes cause data corruption on writes

Answer: B. Every index must be updated on every INSERT, UPDATE, or DELETE, adding cumulative write overhead

Explanation: Each additional index adds its own maintenance cost to every write operation affecting its column, so a table with many indexes pays that cost repeatedly on every single write.

Quick Revision Points

• Indexes help most on high-cardinality columns used in WHERE, JOIN, and ORDER BY.
• Indexes can hurt performance on write-heavy tables, low-cardinality columns, and very small tables.
• Cardinality (the number of distinct values relative to row count) is a key factor in deciding whether an index will be genuinely useful.

Conclusion

• Indexing is a genuine tradeoff, not an unconditional performance win.
• High-cardinality columns used in WHERE, JOIN, and ORDER BY are strong index candidates.
• Low-cardinality columns, very small tables, and write-heavy tables are scenarios where indexing provides limited or even negative value.

While indexes clearly benefit queries filtering or sorting on high-cardinality columns used frequently in WHERE clauses, JOIN conditions, or ORDER BY, they are not an unconditional performance win. Every index adds write overhead, since it must be maintained on every relevant INSERT, UPDATE, or DELETE, making excessive indexing a genuine concern on write-heavy tables. Indexes on low-cardinality columns, which have only a handful of distinct values, often provide little benefit and may be ignored entirely by MySQL's query optimizer, while very small tables see negligible benefit from indexing at all, since a full scan is already fast. Effective indexing requires a deliberate, evidence-based approach using tools like EXPLAIN, rather than an assumption that more indexes are always better.