How database indexing works (with MySQL examples) - Rocketeers app [ Rocketeers ](/) [Login](https://rocketeersapp.com/login) On this page Knowledge --------- How database indexing works (with MySQL examples) ================================================= ### [\#Performance](https://rocketeersapp.com/knowledge/performance) An index is the single biggest lever you have on query speed. Here is what a database index actually is, how it works under the hood, and how to add the right ones in MySQL. Published by [Mark van Eijk](https://rocketeersapp.com/author/mark-van-eijk) on June 23, 2026 · 3 minute read 1. [What is a database index](#content-what-is-a-database-index) 2. [How indexes work under the hood](#content-how-indexes-work-under-the-hood) 3. [Creating an index in MySQL](#content-creating-an-index-in-mysql) 4. [Verify the index is actually used](#content-verify-the-index-is-actually-used) 5. [What to index](#content-what-to-index) 6. [Composite indexes and the leftmost-prefix rule](#content-composite-indexes-and-the-leftmost-prefix-rule) 7. [The cost of indexes](#content-the-cost-of-indexes) 8. [Common mistakes](#content-common-mistakes) 9. [Conclusion](#content-conclusion) When a query gets slow, the cause is almost always a missing index. Indexing is the highest-impact thing you can do for database performance, yet it stays a little mysterious. This guide explains what an index is, how it speeds up reads, and how to add and verify indexes in MySQL. [\#](#content-what-is-a-database-index "Permalink")What is a database index --------------------------------------------------------------------------- An index is a separate, sorted data structure that lets the database find rows without scanning the whole table. The classic analogy is the index at the back of a book. To find every mention of "InnoDB", you don't read all 400 pages, you flip to the index, jump to "I", and get the exact page numbers. A database index does the same thing for your rows. Without an index, MySQL has to do a **full table scan**, reading every row to find the ones that match. On a few hundred rows that's instant. On a few million it's the difference between 2 milliseconds and 2 seconds. [\#](#content-how-indexes-work-under-the-hood "Permalink")How indexes work under the hood ----------------------------------------------------------------------------------------- Most MySQL indexes (everything in InnoDB by default) are stored as a **B-tree**: a balanced tree that keeps values in sorted order and stays shallow even for huge tables. Because the tree is sorted and balanced, MySQL finds any value in a handful of steps instead of a linear scan. A table with a million rows is only a few levels deep, so a lookup touches a handful of nodes rather than a million rows. That same sorted structure is also why an index can satisfy range conditions (`>`, `string('email')->unique(); // unique index $table->index('last_login_at'); // plain index ``` [\#](#content-verify-the-index-is-actually-used "Permalink")Verify the index is actually used --------------------------------------------------------------------------------------------- Adding an index is only half the job, you need to confirm MySQL uses it. Put `EXPLAIN` in front of your query: ``` EXPLAIN SELECT * FROM users WHERE email = 'jane@example.com'; ``` Look at two columns: - **`type`** — `ALL` means a full table scan (bad). `ref`, `eq_ref`, or `const` means an index is being used (good). - **`key`** — the index MySQL chose. `NULL` here means no index was used. If `key` is `NULL` after you added an index, the query usually isn't written in an index-friendly way (see common mistakes below). [\#](#content-what-to-index "Permalink")What to index ----------------------------------------------------- Index the columns that appear in: - **`WHERE` filters** — `WHERE status = 'active'` - **`JOIN` conditions** — the foreign-key columns on both sides - **`ORDER BY` / `GROUP BY`** — an index can return rows already sorted, skipping a separate sort step Foreign keys are a common blind spot. A column like `posts.user_id` used in joins should almost always be indexed, otherwise every join does a scan. [\#](#content-composite-indexes-and-the-leftmost-prefix-rule "Permalink")Composite indexes and the leftmost-prefix rule ----------------------------------------------------------------------------------------------------------------------- When a query filters on several columns together, a **composite index** beats several single-column ones: ``` ALTER TABLE orders ADD INDEX idx_orders_user_status (user_id, status); ``` This index helps queries that filter on `user_id`, or on `user_id` **and** `status` together: ``` SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'; ``` The catch is the **leftmost-prefix rule**: MySQL can only use the index left-to-right. The index above helps `user_id` alone, and `user_id + status`, but **not** `status` alone, because `status` isn't the leftmost column. Order the columns by how you actually query them. [\#](#content-the-cost-of-indexes "Permalink")The cost of indexes ----------------------------------------------------------------- Indexes are not free, so don't index every column: - **Slower writes** — every `INSERT`/`UPDATE`/`DELETE` must update each index. Over-indexing a write-heavy table hurts. - **Disk and memory** — indexes take space and compete for the InnoDB buffer pool. - **Maintenance** — redundant indexes (e.g. an index on `(a)` when you already have `(a, b)`) waste resources for no gain. A good rule of thumb: add indexes to support your real, slow queries, then remove ones that `EXPLAIN` never chooses. [\#](#content-common-mistakes "Permalink")Common mistakes --------------------------------------------------------- - **Wrapping the column in a function** — `WHERE DATE(created_at) = '2026-06-23'` can't use an index on `created_at`. Rewrite as a range: `WHERE created_at >= '2026-06-23' AND created_at < '2026-06-24'`. - **Leading wildcards** — `WHERE name LIKE '%smith'` can't use an index; `LIKE 'smith%'` can. - **Indexing low-cardinality columns** — an index on a boolean or a `status` with two values rarely helps on its own. - **Hitting the key-length limit** — very long `VARCHAR` indexes can fail on older MySQL. See [MySQL 1071: specified key was too long](/mysql-1071-specified-key-was-too-long). [\#](#content-conclusion "Permalink")Conclusion ----------------------------------------------- Indexing is the first place to look when a query is slow. Find the columns in your `WHERE`, `JOIN`, and `ORDER BY` clauses, add a focused index, and confirm with `EXPLAIN` that MySQL uses it, while keeping an eye on write cost so you don't over-index. If you're still on an older MySQL release, upgrading also unlocks better indexing limits and a smarter optimizer, see [upgrading MySQL 5.7 to 8.0 on Ubuntu](/upgrade-mysql-5-7-to-8-0-ubuntu). ### Subscribe to our newsletter Do you want to receive regular updates with fresh and exclusive content to learn more about web development, hosting, security and performance? Subscribe now! Fill in your email address to receive updates Subscribe #### More in [\#Performance](https://rocketeersapp.com/knowledge/performance) - [How to optimize server performance](https://rocketeersapp.com/knowledge/optimize-server-performance) - [How to optimize website performance](https://rocketeersapp.com/knowledge/optimize-website-performance) - [How to measure TTFB (Time To First Byte)](https://rocketeersapp.com/knowledge/ttfb) - [How to enable and configure OPcache for faster PHP](https://rocketeersapp.com/knowledge/enable-opcache-php) - [A complete guide to caching in Laravel](https://rocketeersapp.com/knowledge/laravel-cache) - [Brotli vs Gzip: which compression should you use?](https://rocketeersapp.com/knowledge/brotli-vs-gzip) [View all 13 articles →](https://rocketeersapp.com/knowledge/performance)