MySQL Index Is Not a Magic Solution: How to Choose an Index Without Burdening the Application

When a MySQL query starts to slow down, adding an index is often the first suggestion. This advice is not wrong, but indexes are not a magic button that automatically solves all database problems. The right index can indeed drastically reduce MySQL's workload. Conversely, unnecessary indexes can increase the size of the database and make insert, update, and delete operations heavier.

Therefore, adding an index should be treated like organizing shelves in a warehouse. Properly placed shelves help find items quickly. However, too many shelves can also make the warehouse cramped, expensive, and difficult when items need to be moved.

What is the actual function of an index?

Without an index, the database might have to check rows one by one to find the requested data. This process is called a full table scan. For small tables, the impact may not be felt. However, when a table contains hundreds of thousands or millions of rows, repeated checks can consume time and resources.

An index stores additional structures that help the database find specific rows without reading the entire contents of the table. A simple example is if an application frequently searches for users by email address, the email column can be indexed so that the search does not need to check all users.

CREATE INDEX idx_users_email ON users (email);

It is important to note that an index is not a complete copy of the table. It is an additional search structure that must be maintained by the database whenever data changes.

When is a column worthy of being indexed?

Do not start with the question “which columns can be indexed?”, but rather with “which queries are run most frequently or are the most expensive?”. Indexes should serve real data access patterns.

Columns frequently used for searching

Columns that often appear in WHERE conditions can be candidates for indexing. For example, online store applications often search for orders based on user_id or status based on status.

SELECT * FROM orders
WHERE user_id = 125;

For such queries, an index on user_id is likely more useful than an index on a column that is rarely used in searches.

Columns used for sorting

Indexes can also help queries that frequently use ORDER BY, especially when combined with search conditions. For example, the admin page displays the latest orders for a specific user.

SELECT id, total, created_at
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC
LIMIT 20;

For this pattern, a composite index on (user_id, created_at) may be more relevant than two separate indexes.

Columns with sufficiently diverse values

Indexes are usually more useful on columns with many different values. Columns like email, invoice_number, or product_id are generally more selective than columns like is_active that only contain two possible values.

However, this is not an absolute rule. An index on a column with little variation can still help in certain combinations, especially if the table is very large or the query has additional conditions.

Understanding composite indexes

A composite index is an index that includes more than one column.

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

The order of the columns is very important. The index (user_id, created_at) primarily helps queries that start filtering from user_id. It is not always effective for queries that only search based on created_at.

Imagine a list of addresses organized by province and then by city. This list is very helpful when we know the province. However, if we only know the city name without the province, the search may not be efficient.

Therefore, before creating a composite index, write down some queries that the application actually uses. Check the columns that always appear in filters, then consider the sorting or range columns afterward.

Use EXPLAIN, don’t guess

MySQL provides EXPLAIN to see how a query will be executed. With this command, developers can find out whether the database is using an index, how many rows are estimated to be read, and what strategy is chosen.

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC
LIMIT 20;

Pay attention to the following:

• key: the index chosen by the optimizer, if any.
• possible_keys: indexes that may be used.
• rows: estimated number of rows that need to be checked.
• type: a description of the data access method, from relatively efficient to ones that need attention.

The result of EXPLAIN is not a single verdict. Queries with a small number of rows may still be fast even if they do not use an index. Conversely, queries that seem simple can become problematic as the data grows. Test with data that closely resembles production conditions, not just with ten rows on a local machine.

Risks of too many indexes

Each index requires additional storage space. Moreover, when a row is added or changed, MySQL needs to update the related indexes. If a table has many unnecessary indexes, write operations can slow down.

Another issue is overlapping indexes. For example, a table has indexes on (user_id) and (user_id, created_at). Both may not be needed, depending on the queries being run. Removing an index should not be done carelessly, but old indexes need to be evaluated after query patterns change.

Indexes also cannot save poorly written queries. Certain functions on columns, searches with non-matching patterns, or conditions that force the database to read too much data can still diminish the benefits of indexes.

What you can do now

1. Note the queries that are called most frequently and the queries that take the longest to complete.
2. Run EXPLAIN on those queries.
3. Check if the filter, sorting, and relationship columns have appropriate indexes.
4. Test new indexes in a testing environment with realistic data volumes.
5. Compare read times and their impact on write operations.
6. Document the reasons for each index created for easy review when the application structure changes.

Healthy practice is not to index every column, but to ensure that each index has a clear reason. A good database is not only fast when reading data but also makes sense when receiving changes every day.

In summary: choose indexes based on real queries, understand the order of columns in composite indexes, and always verify with measurements. With this approach, indexes become tools to guide the database to work more efficiently, not just patches when the application starts to feel slow.

– Rio Yotto @rioyotto