Insights/Digital Product

MySQL Indexing Guide

MySQL indexing example with query plan and database table on monitor

MySQL indexing can make the difference between a fast app and a slow one. If your database has to read every row to answer a simple query, performance drops fast as data grows. This guide explains how indexes work, how to create them in MySQL, and when they help or hurt.

If you are building a product that depends on search, filtering, dashboards, or reporting, database decisions matter early. At Refact, we see this often in custom platforms and internal tools where query speed affects real user experience. If you need help planning a data-heavy product, explore our custom software partner services.

How database indexing works

Imagine we have a table of users with names and salaries:

--------------------------------------
| id | name  | salary |
--------------------------------------
| 1  | Alice | 50000  |
| 2  | Bob   | 60000  |
| 3  | Sam   | 55000  |
| 4  | Eve   | 70000  |
| 5  | Carol | 45000  |
| 6  | Dave  | 58000  |
--------------------------------------

Querying without an index

Suppose we run SELECT * FROM users WHERE name='Sam'. Without an index, MySQL checks rows one by one until it finds a match. This is called a full table scan.

With six rows, that is fine. With millions of rows, it becomes expensive. The same idea also affects many UPDATE and DELETE queries because the database still needs to find the right rows first.

Querying with an index

An index is a separate data structure that helps the database find rows faster. In MySQL, indexes are commonly stored as B-tree structures. You can think of an index like the index at the back of a book. Instead of reading every page, you go straight to the part you need.

When you add an index to the name column, MySQL organizes those values in sorted order. The actual row data stays in the table, but the index gives the database a faster path to the right location.

Here is a simplified view of the indexed order:

--------------------------------------
| id | name  | salary |
--------------------------------------
| 1  | Alice | 50000  |
| 2  | Bob   | 60000  |
| 5  | Carol | 45000  |
| 6  | Dave  | 58000  |
| 4  | Eve   | 70000  |
| 3  | Sam   | 55000  |
--------------------------------------

A B-tree lets MySQL narrow the search quickly instead of checking each row in sequence.

Simple B-tree search example

Step 1: MySQL checks a middle value in the index, such as Dave. Since Sam comes after Dave, it can ignore the earlier part of the index.

--------------------------------------
| id | name  | salary |
--------------------------------------
| 4  | Eve   | 70000  |
| 3  | Sam   | 55000  |
--------------------------------------

Step 2: It checks the remaining section and narrows again.

--------------------------------------
| id | name  | salary |
--------------------------------------
| 3  | Sam   | 55000  |
--------------------------------------

Step 3: It finds the matching row with far fewer comparisons than a full scan.

How indexes affect speed

Indexes usually make reads faster, especially for SELECT queries with filtering, sorting, or joins. They can also help UPDATE and DELETE because those statements still need to locate rows.

But indexes are not free. Every insert or update may also need to update the index. That means write-heavy tables can slow down if you add too many indexes. Good indexing is about tradeoffs, not adding an index to every column.

How to create an index in MySQL

The basic syntax is simple:

CREATE INDEX index_name
ON table_name(column_list);

You provide the index name, the table name, and the column or columns to include.

Here is a basic example with a users table:

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    salary INT NOT NULL
);

CREATE INDEX idx_users_salary
ON users(salary);

This creates an index on the salary column. If you often filter users by salary, this can reduce query time.

Now insert some sample data:

INSERT INTO users (id, name, salary)
VALUES (1, 'Alice', 50000),
       (2, 'Bob', 60000),
       (5, 'Carol', 45000),
       (6, 'Dave', 58000),
       (4, 'Eve', 70000),
       (3, 'Sam', 55000);

To see how MySQL plans to run a query, use EXPLAIN:

EXPLAIN
SELECT
    id,
    name,
    salary
FROM
    users
WHERE
    salary = 60000;

If you are building APIs on a JavaScript backend, query planning matters just as much as schema design. Slow database access often shows up first in app response times, especially in Node.js development projects with search, filters, or reporting.

Adding multicolumn indexes to a MySQL table

MySQL also supports multicolumn indexes. The order of columns matters because MySQL sorts by the leftmost column first, then the next column, and so on.

For example:

CREATE INDEX idx_users_name_salary
ON users(name, salary);

This index can help when your query matches the same left-to-right order.

Queries where this multicolumn index can be used

  1. Filtering by the leftmost column only:
SELECT *
FROM users
WHERE name = 'Sam';
  1. Filtering by both indexed columns:
SELECT *
FROM users
WHERE name = 'Sam' AND salary > 3000;

Queries where this multicolumn index is less useful

  1. Filtering only by the second column:
SELECT *
FROM users
WHERE salary > 3000;
  1. Using OR across conditions that do not match the index well:
SELECT *
FROM users
WHERE name = 'Sam' OR salary > 3000;

This leftmost-prefix rule is one of the most important ideas in indexing. It also comes up in other relational databases. If your product may outgrow MySQL or already uses mixed systems, our PostgreSQL development work follows many of the same performance principles.

How to list all indexes on a table

You can inspect indexes with SHOW INDEX:

SHOW INDEX FROM users;

Or query the system table directly:

SELECT
    INDEX_NAME
FROM
    information_schema.statistics
WHERE
    TABLE_NAME = 'users';

Both approaches help you review what already exists before adding more.

How to remove an index

To remove an index, use DROP INDEX:

DROP INDEX idx_users_salary ON users;

Always verify syntax for your MySQL version. In real projects, removing a bad or duplicate index can improve write performance and reduce maintenance overhead.

Three practical tips for multicolumn indexes

Tip 1: Put the most useful filtering column first. If most queries filter by name, start with name.

CREATE INDEX idx_users_name_salary ON users(name, salary);

Tip 2: Put equality checks before range checks when possible. That usually gives the planner a better path.

CREATE INDEX idx_users_name_salary ON users(name, salary);

Tip 3: Avoid overlapping indexes unless you have a clear reason. If you already have (name, salary), a separate index on name may be redundant.

DROP INDEX idx_users_name ON users;

Indexing principles that improve query performance

Good indexes support real query patterns. They are not guesses. Start with the queries your app runs most often, then design around them.

Fast lookup at larger scale

Without an index, growth hurts. A query that feels fine with 1,000 rows may become painful with 1,000,000. Indexes reduce the amount of data MySQL has to inspect.

That said, indexes also add storage and write overhead. The goal is not maximum indexing. The goal is useful indexing.

Scan in one direction

Indexes also help when a query filters and sorts in a compatible order.

SELECT * 
FROM users 
WHERE age >= 30 
ORDER BY age ASC 
LIMIT 2;

If there is an index on age, MySQL can jump to the first matching row, then continue forward until it has enough results. That is much faster than filtering everything, sorting everything, and then limiting the output.

These decisions are part of broader backend architecture. Choosing the right technology stack means thinking about database behavior, app traffic, and future reporting needs together.

Considerations for advanced index design

Real queries are rarely just one simple WHERE clause. They join tables, sort results, group records, and apply ranges. Good index design starts with how the query actually runs.

Inequality conditions

Indexes are strong at finding exact values. They are less efficient for broad not-equal conditions like WHERE role != 'ceo' because a large part of the index may still need to be scanned.

Sometimes a compound index can still help if another indexed column narrows the result set first.

Pattern matching with LIKE

Indexes can help with prefix matches such as:

SELECT *
FROM users
WHERE name LIKE 'Caro%';

This works because the search starts from the left side of the value. But a leading wildcard, such as LIKE '%aro', usually prevents efficient index use.

ORDER BY

Sorting large result sets is expensive. If your ORDER BY clause matches an index, MySQL may avoid extra sorting work. That can make a big difference in dashboards, exports, and user-facing tables.

GROUP BY and DISTINCT

Aggregation queries also benefit from careful indexing. If you group or deduplicate on certain columns often, the right index can reduce sorting and scanning work.

Table joins

Joins often become slow when the join columns are not indexed. Here is a simple example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department_id INT NOT NULL
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL
);

CREATE INDEX idx_department_id ON employees(department_id);

SELECT e.name AS employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

In this case, indexing employees.department_id helps MySQL match rows faster during the join. Without the right indexes, joins can fall back to much heavier scans.

When databases support operational workflows, poor indexing often creates downstream problems in reporting, admin tools, and sync jobs. That is one reason many teams invest in better automation and integration once manual work starts piling up.

Conclusion

Indexes help MySQL find data faster, but only when they match real query patterns. The basics are simple: use indexes for common filters, pay attention to column order in multicolumn indexes, and avoid redundant indexes that add overhead without clear benefit.

  • Use single-column indexes for common lookups
  • Use multicolumn indexes when queries follow a clear left-to-right pattern
  • Check execution plans with EXPLAIN
  • Balance read speed against write cost
  • Review old indexes before adding new ones

If you are planning a data-heavy app, internal tool, or reporting workflow, database choices should not be an afterthought. Learn more about Refact as a product development studio, or talk with Refact about building software that stays fast as it grows.

Share
MySQL Indexing Guide | Refact