Why More Indexes in MySQL Aren't Always Better

🛑 When Indexing Can Hurt MySQL Performance

Indexes usually improve query performance, but in some cases, they can actually make things worse. Here are the key situations:

🔻 1. Too Many or Unused Indexes

• Issue: Each INSERT, UPDATE, or DELETE must update all indexes.
• Example: A table has 10 indexes, but most of them are rarely used.
• Impact:
  • Slower write performance.
  • Increased disk space usage.
  • Slower replication or backup.

🔻 2. MySQL Picks the Wrong Index

• Issue: MySQL's query planner might choose an inefficient index.
• Example: Index on (status, created_at), but you're filtering only on created_at. If status has low selectivity, it can confuse the planner.
• Impact: Poor query plan — slower than a full table scan.

🔻 3. Low Selectivity Indexes

• Issue: Indexes on columns with very few unique values (like is_active, gender, etc.) are not effective.
• Example: 99% of rows have is_active = 1.
• Impact: MySQL may still scan almost the entire table — worse than not using the index.

🔻 4. Index Causes Random I/O

• Issue: Using the index for lookups, but the data rows are scattered on disk.
• Example: Index is used, but every row fetch causes a disk seek (common on HDD).
• Solution: Use a covering index — include all columns used in the SELECT clause.

🔻 5. Indexes + ORDER BY + LIMIT Inefficiency

• Issue: If the index doesn't support the ORDER BY, MySQL may scan lots of rows to sort and limit.
• Example: ORDER BY created_at DESC LIMIT 10 on an index like (user_id, created_at) won't be efficient.
• Impact: High memory or disk usage during sorting.

🔻 6. Bulk Inserts or Updates with Many Indexes

• Issue: Each index must be updated during large inserts or updates.
• Example: Inserting 100,000 rows into a table with 5 indexes = 500,000 index operations.
• Solution:
  • Drop indexes temporarily and recreate them afterward.
  • Use LOAD DATA INFILE for faster import.

🔻 7. Wrong Index Type

• Issue: Using FULLTEXT, SPATIAL, or HASH indexes on unsupported or inappropriate column types.
• Impact: No performance gain or even query failure.

✅ Tips to Avoid Index Problems

• Use EXPLAIN to analyze query plans.
• Drop unused or redundant indexes.
• Avoid indexing low-selectivity columns unless absolutely needed.
• Use ANALYZE TABLE periodically to update index statistics.
• Monitor insert/update/delete performance if your schema has many indexes.

Let’s walk through a concrete example where indexing hurts performance in MySQL.

🧪 Example Scenario: Index Hurts Performance

👇 Table: users

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255),
  status ENUM('active', 'inactive'),
  created_at DATETIME,
  INDEX idx_status (status)
);

🔍 Query:

SELECT * FROM users WHERE status = 'active';

📊 Data:

• 1,000,000 rows total.
• 990,000 have status = 'active'.
• Only 10,000 have status = 'inactive'.

🔎 What MySQL Does:

Because there is an index on status, MySQL may try to use the index.

BUT since status = 'active' returns 99% of the table, using the index doesn't help much. Instead, MySQL:

• Uses the index to find row IDs (ids),
• Then fetches each row from disk individually (causing random I/O),
• Ends up reading almost the entire table anyway.

⚠️ Why It's Slower Than a Full Table Scan

• With index: MySQL does millions of tiny reads (index lookup → row fetch).
• Without index: MySQL could just scan the table linearly in memory/disk — much faster for this type of query.

✅ How to Fix

1. Remove the index on status if it's rarely helpful.
2. Let MySQL do a full table scan — it’s faster when most rows match.

3. If you often query status = 'inactive' (which is rare), consider a partial index or a composite index like:

   CREATE INDEX idx_inactive_created ON users(created_at)
   WHERE status = 'inactive';
   

📌 Summary

Indexing hurts performance when:

• The index has low selectivity (many duplicates),
• MySQL fetches most of the table anyway,
• It causes random I/O instead of efficient sequential reads.