Bulk imports went from minutes to over ten minutes—are there too many indexes on the table?

Indexes can indeed be over-added. Every secondary index must be maintained synchronously on insert, update, and delete, so bulk imports, single-row write P99, and replication lag on write-intensive tables can all worsen together. Based on common 2026 delivery experience, 5–8 indexes per table are common for read-heavy, write-light background tables; 3–6 for mixed business tables; and 2–4 for write-intensive log tables. But the count is only a signal. Duplicate indexes, low-selectivity indexes, and single-column indexes that can be covered by a composite index are the priority cleanup targets. Whether an index should be dropped starts with whether it maps to an identifiable high-frequency query, then whether the write cost is acceptable.

Why indexes slow down writes

When InnoDB writes a row, it does more than place a record in the clustered index; it also updates every secondary index. The more indexes there are, the more B+ tree pages a write transaction must modify, and the more redo, undo, and buffer pool it consumes. When indexed columns are updated, index maintenance happens again. The relationship is not simply linear, but the higher the write frequency, the more noticeable the cost of index count becomes.

A common situation in projects is this: when an order log table, log table, or message table first goes live, there are few queries, so developers add an index for every condition. Half a year later, bulk imports go from a few minutes to over ten minutes, and replication lag stretches from seconds to minutes. At that point, adding machines or connections alone often cannot keep up, because the bottleneck is write amplification and index maintenance.

• Write amplification: Each additional secondary index means one more index page to write on insert and update.
• Page splits: Indexes with random writes are more likely to trigger page splits, affecting sustained write throughput.
• Cache footprint: Index pages crowd the buffer pool, reducing hot data hit rates.
• Locks and transactions: Index maintenance makes write transactions longer, and lock hold time may stretch.
• DDL cost: Adding or changing indexes later is also slower, making release windows harder to schedule.

How to decide which indexes to drop: the three-check, one-verify method

Index governance cannot rely on feeling, nor should it focus only on count. I tend to use a practical framework: three checks, one verification. First check redundancy, then selectivity, then read/write ratio, and finally verify with execution plans and index usage statistics. Each step should leave evidence to avoid mistakenly dropping an index that a core query depends on.

1. Check redundancy: Two indexes with the same prefix, where the later columns are covered by another composite index, or a primary key column that also has a separate index, are usually candidates for consolidation.
2. Check selectivity: For low-cardinality columns such as gender, status, or boolean values, a standalone index is often less useful than folding it into a composite index; composite indexes typically put high-selectivity columns first.
3. Check read/write ratio: For write-heavy, read-light tables, fewer indexes are better; for read-heavy, write-light background tables, more query-covering indexes can be kept.
4. Verify usage: Check execution plans, slow queries, and index usage statistics, confirm view meanings against official database documentation, and observe a full business cycle before deciding.

The acceptance bar can be set like this: before dropping an index, there is an execution plan comparison; after dropping it, core API P95 does not rise noticeably; write time improvement can be explained with quantitative metrics; and a rollback script and low-peak window are ready. If these cannot be met, do not drop production indexes directly during the day.

On-site delivery: index cleanup when bulk imports slow down

In a 2026 project delivery, an order log table had 11 indexes at the time, 3 of which were single-column indexes with duplicate prefixes and 2 were low-selectivity status indexes. The constraints were no write stoppage during the day, replication lag already at minute level, and bulk import time around 12 minutes. The approach was to first verify high-frequency queries with execution plans during a low-peak window, merge duplicate indexes into one composite index, keep low-selectivity indexes only at the end of the composite index, and prepare a rollback script. As a result, bulk import time dropped to around 7 minutes and write P99 fell, but the trade-off was a short-term rise in replication lag during that night's DDL, requiring off-peak observation. Experience range: write-intensive tables keep 2–4 indexes, and after cleanup, write improvement is usually measured by bulk import time and P99.

Before adding an index, look for alternatives in this order

Not every slow query needs a new index. A safer order is to rewrite the query first, then see whether it can be folded into an existing composite index, and only then consider adding a single-column index. Many so-called missing indexes are actually SQL scanning too many rows, returning too many columns, or having composite index columns in the wrong order.

• First reduce scanned rows: Add equality conditions, avoid wrapping columns in functions, and avoid implicit type conversions.
• Then fold into a composite index: Check whether high-frequency query conditions can be covered by the leftmost prefix of an existing composite index, keeping range columns as late as possible.
• Then consider a covering index: Let queries read only index pages to reduce table lookups; but too many covered columns increase index size.
• Then evaluate archiving and partitioning: After hot/cold separation of historical data, index pressure may naturally decrease.
• Only then add a single-column index: Before adding, confirm it will not be replaced by an existing index and that the write cost is acceptable.

Reversing the order has a direct cost: a pile of indexes is added, writes get slower, yet queries do not use the new indexes because of column order or insufficient selectivity. Going back to drop them then requires another round of execution plan verification and low-peak release.

Experience ranges for index counts on different tables

There is no universal standard for index count, but experience ranges can be drawn by table read/write characteristics. The numbers below are suitable for review, not as hard targets. Whether an index should stay ultimately depends on whether it serves an identifiable high-frequency query.

• Read-heavy, write-light background tables, configuration tables, and report dimension tables: 5–8 indexes per table are common, with the focus on covering high-frequency queries and sorting.
• Mixed business main tables, such as orders, users, and products: 3–6 are common, prioritizing composite indexes to cover multiple queries.
• Write-intensive log tables, such as logs, events, messages, and tracks: 2–4 are common, keeping the primary key and a small number of key query indexes.
• Small tables or tables with very high cache hit rates: A few more indexes have limited impact on writes, so there is no need to force deletions just for count.

The key here is not memorizing numbers, but building a habit: before adding an index, ask which identifiable high-frequency query it corresponds to. If you cannot answer, it is likely a future cleanup candidate.

Applicable scenarios and boundaries

Signals suitable for index governance include: write latency keeps rising, bulk imports slow down noticeably, replication lag expands, the number of indexes on a single table clearly exceeds the number of high-frequency queries, and slow queries are concentrated in writes rather than reads. In these cases, cleaning up redundant indexes with the three-check, one-verify method is usually more direct than continuing to add hardware.

Cases not suitable for major changes should also be clear: read-heavy, write-light workloads; tables with only tens of thousands to hundreds of thousands of rows; core queries that already depend on these indexes; and deletions that would push queries from index scans back to full table scans. The boundary sentence can be remembered as: If dropping an index turns a core query from an index scan into a full table scan, it is usually not worth it even if writes improve; if the table is small and cache hits are high, a few more indexes have limited impact on writes.

Common pitfalls and acceptance criteria

The most common pitfall in index governance is treating an index with no recorded usage as useless. Index usage statistics have window bias: month-end reports, quarterly reconciliations, and background exports may run only once every few weeks. Before dropping, it is best to observe a full business cycle, covering at least one month-end close or peak scenario.

• Dropping production indexes directly: Without execution plan comparison and a rollback script, rolling back during evening peak is difficult.
• Looking only at count, not queries: Over-deleting on read-heavy, write-light tables can hurt query performance.
• Stacking single-column indexes: In scenarios that can be covered by a composite index, splitting into multiple single-column indexes is more wasteful.
• Ignoring DDL impact: Adding or dropping indexes itself consumes IO and may increase replication lag.
• Misdiagnosing the bottleneck: High replication lag may also come from large transactions, bulk imports, or DDL, not necessarily index count.

Acceptance criteria are best written as three points: core query execution plans do not regress, write time or import time improvement is quantifiable, and the rollback script remains usable within one release cycle. Only when all three are met is it a qualified index cleanup.

FAQ

Do more indexes make inserts slower?

Yes. For each additional secondary index on a write-intensive table, inserts and updates must maintain one more index page. Common symptoms are increased bulk import time, higher write P99, and greater replication lag.

How do I know whether an index is being used?

Use execution plans, slow queries, and index usage statistics together, and check view semantics against official database documentation. Statistics have window bias, so it is best to observe a full business cycle before concluding.

Can a composite index replace multiple single-column indexes?

Partly. When high-frequency query conditions can be covered by the leftmost prefix of a composite index, a separate single-column index is usually unnecessary; but sorting, ranges, and selectivity change usability, so execution plans still matter.

How many indexes per table is appropriate?

There is no fixed number. Read-heavy, write-light tables commonly have 5–8; mixed workloads 3–6; and write-intensive log tables 2–4. These are experience ranges, and the final criterion is whether there is a clear high-frequency query.

Do I need downtime to drop indexes?

Most online DDL can run during low-peak business hours, but it consumes IO and may increase replication lag. It is advisable to run during low peak, in batches, and with a rollback script; check official database documentation for specifics.

If in 2026 you encounter slower writes and a single table clearly has too many indexes, first run a round of three-check, one-verify, and put candidate indexes, execution plans, and rollback scripts together into the release ticket. But if the table is small, read-heavy and write-light, or dropping an index would send a core query to a full table scan, there is no need to touch it just to make the count look good. In project delivery, the index list and verification records are usually part of the database change.