Lesson 28 of the SQL Optimization Course: The Impact of Index Quantity on MySQL Write Performance

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Experimental Setup

This study examines how increasing index counts affect write operations (INSERT/UPDATE/DELETE) in MySQL. A controlled environment was established using a sandbox instance (3500 port) with 10 identically structured tables (t1 to t10). Each table had 11 fields (id + r0 to r10), but varied in secondary index quantity (t1=1 index, t10=10 indexes).

Table Schema:

1
2
3
4
5
6
7
8
CREATE TABLE `t0` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `r0` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  ...
  `r10` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Write Operation Benchmarks

1. INSERT Performance

Inserting 100,000 rows into tables with varying index counts:

1
2
CALL sp_batch_write('insert', 't1', 100000); -- 1 min 24.61 sec  
CALL sp_batch_write('insert', 't10', 100000); -- 2 min 32.32 sec  

Observation: Each additional index increased insertion time by ~10-20%.

2. UPDATE Performance

Updating 100,000 rows across all indexes:

1
2
CALL sp_batch_write('update', 't1', 100000); -- 22.81 sec  
CALL sp_batch_write('update', 't10', 100000); -- 4 min 43.74 sec  

Impact: Index updates became exponentially slower (t10 was ~12x slower than t1).

3. DELETE Performance

Deleting 100,000 rows (highlighting redo log overhead):

1
2
CALL sp_batch_write('delete', 't1', 100000); -- 31.80 sec  
CALL sp_batch_write('delete', 't10', 100000); -- 4 min 22.88 sec  

Key Insight: DELETE triggers page splits and purging, amplified by index fragmentation.

4. LOAD DATA Performance

Importing 100,000 rows with indexes:

1
2
LOAD DATA INFILE ... INTO TABLE t1; -- 11.01 sec  
LOAD DATA INFILE ... INTO TABLE t10; -- 45.79 sec  

Result: Index maintenance added ~4x overhead to bulk imports.

Conclusion & Recommendations

Index Overhead: Each additional index increases write amplification (CPU, I/O, and storage costs).
Business Tradeoffs: Prioritize indexes based on query patterns (e.g., retain only PK on primary nodes for write-heavy workloads).
Maintenance Tips:
- Use TRUNCATE instead of DELETE for mass deletions.
- Regularly optimize tables to rebuild fragmented indexes.

Future Work: Explore InnoDB adaptive hashing and partitioning strategies for high-write environments.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.