---
title: MySQL Index
description: A structure that lets a query find rows without scanning the whole table. The wrong index slows writes.
canonical: https://penasihathosting.com/en/hosting-wiki/mysql-index
type: wiki
locale: en
updated: 2026-10-29
author: Willya Randika
---

# MySQL Index

## Overview

- **Summary:** A structure that lets a query find rows without scanning the whole table. The wrong index slows writes.
- **Author:** Willya Randika ([profile](/penulis/willya-randika))

## Article

**A MySQL index** is a data structure (usually a B-tree) that lets the database find rows without scanning the whole table. Without an index, a `WHERE` or `JOIN` query can go from milliseconds to seconds as data grows.

## When an index helps

| Query | Relevant index |
| --- | --- |
| `WHERE email = ?` | Index on `email` |
| `WHERE status = ? ORDER BY created_at` | Composite `(status, created_at)` |
| `JOIN orders ON orders.user_id = users.id` | Index on `orders.user_id` |
| `WHERE slug = ?` | Unique index on `slug` |

## Composite indexes and column order

An index on `(status, created_at)` can serve a `status` filter alone, or `status` plus `created_at` ordering. It cannot serve `created_at` alone. Column order follows your query pattern, not just a list of columns.

## How to check

Use `EXPLAIN` before and after adding an index:

```sql
EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;
```

Look at `type`, `key`, and `rows`. `ALL` means a full scan; `ref` or `range` is usually better.

## What to watch for

- **Indexes slow writes.** Every `INSERT`, `UPDATE`, and `DELETE` must update the index.
- **Do not index every column.** Choose the ones used in filters, joins, and ordering.
- **Avoid functions on indexed columns.** `WHERE YEAR(created_at) = 2026` often skips the index.
- **Low cardinality is weak.** A column with few unique values is rarely useful on its own.
- **Back up before changing the schema.** Test on staging, then measure the query after the change.

Measure with a query monitor before and after the change.

## FAQ

<Accordion>
  <AccordionItem title="Should every column have an index?">
    No. An index no query uses only adds write cost and disk space.
  </AccordionItem>
  <AccordionItem title="What is a composite index?">
    An index with several columns, such as `(user_id, created_at)`. Column order decides which queries can use it.
  </AccordionItem>
  <AccordionItem title="Why is the query still slow after adding an index?">
    The column order may not match, a function may wrap the column, or the optimizer may choose a full scan because the table is small.
  </AccordionItem>
</Accordion>