Suggest an edit

Improve this article

Refine the answer for “Why aren't indexes always useful?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

Indexes aren't always useful because, besides speeding up search, they **add overhead** to storing and updating data.

### The main reasons

1. **They slow down write operations.**
On every `INSERT`, `UPDATE`, or `DELETE`, the database has to update not just the table itself but every related index.
The more indexes there are, the slower changes run.
2. **They take up extra space.**
Indexes are stored separately from the table and can take up a lot of space, especially when indexing large text or numeric fields.
3. **They may go unused.**
If a query selects too much of the table (e.g. >20-30% of rows),
the optimizer may decide a **full scan** is faster than going through the index.
4. **They complicate optimization.**
With many indexes, the optimizer can pick a "bad" one,
and the query ends up running even slower than without an index.
5. **They're inefficient for frequently changing data.**
In tables with a high rate of updates, indexes fragment quickly,
which reduces their benefit and requires periodic maintenance (rebuild/reorganize).

**Summary:**
Indexes are useful **only where reads are frequent and changes are rare**,
and where filters or sorts actually use the indexed fields.
Otherwise, they **slow the system down instead of speeding it up**.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts204 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.