Website Slow Even Though There's Not Much Data? Understand the Role of Index in MySQL

When a website starts to slow down, attention usually turns immediately to the server size, internet connection, or the framework being used. However, one often overlooked cause is how the database searches for data. MySQL can work very quickly, but without the right index, a simple search can turn into a check of thousands or millions of rows.

An index is an additional structure that helps the database find data without reading the entire contents of the table. It's like looking for a name in a phone book; we don't need to open every page from the beginning if the list of names is already sorted. However, an index is not a solution that can be added excessively. Indexes also take up storage space and make the processes of adding, modifying, or deleting data slightly heavier.

What does an index actually do?

Imagine a table orders containing millions of orders. If the application runs the following query:

SELECT * FROM orders WHERE user_id = 125;

Without an index on the user_id column, the database may have to check each row one by one to find the orders belonging to that user. This process is called a table scan or full table scan.

If an index is available on user_id, MySQL can use the index structure to directly narrow down the location of the relevant data. As a result, the number of rows that need to be checked can be significantly reduced.

CREATE INDEX idx_orders_user_id ON orders (user_id);

It is important to note that an index does not automatically make all queries fast. The database still has to decide whether the index is worth using based on the query conditions and data distribution.

Which columns typically need to be indexed?

Columns that are frequently used in the following query patterns usually become candidates for indexing:

• WHERE, to filter data based on specific values.
• JOIN, to connect data from two tables.
• ORDER BY, when the application often sorts data based on certain columns.
• GROUP BY, in certain conditions when grouping is done repeatedly.
• UNIQUE, such as email addresses or transaction codes that must be unique.

Primary keys generally already have an index. Therefore, adding a second index on a primary key column usually does not provide any benefit.

A common example is the user's order list page:

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

This query not only filters based on user_id, but also sorts based on created_at. A composite index may be more appropriate:

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

The order of columns in a composite index is important

A composite index is an index that consists of multiple columns. The order of the columns should not be chosen randomly. In the previous example, user_id is placed first because it is used to filter data, and then created_at is used to assist with sorting.

Simply put, an index with the arrangement (user_id, created_at) is suitable for queries that search based on user_id. However, that index may not be effective for queries that only search based on created_at.

The following example has different requirements:

SELECT * FROM orders
WHERE status = 'pending'
AND created_at > '2026-09-01';

An index that may be relevant is (status, created_at), especially if the application often displays orders based on status and time range. Nevertheless, the best choice still depends on the amount of data and actual query patterns.

Use EXPLAIN before guessing

Instead of immediately adding an index, use EXPLAIN to see how MySQL executes the query.

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

The results of EXPLAIN can help identify several important aspects:

• type: an overview of the data access method used.
• possible_keys: indexes that may be considered.
• key: the index that is actually chosen.
• rows: an estimate of the number of rows that need to be checked.
• Extra: additional information, including possible sorting or scanning operations.

If the query only returns 20 rows but MySQL estimates it has to check hundreds of thousands of rows, that is a sign that the query or index needs further examination. The numbers in EXPLAIN are estimates, not always the actual count, but they are still useful for identifying major issues.

Common mistakes when adding indexes

Indexing all columns

Adding an index to every column may seem safe, but it can actually increase the database size and slow down write operations. Every time a row is added or modified, the related indexes also need to be updated.

Ignoring columns with low value variation

Columns like is_active that only contain 0 and 1 are often not very selective when standing alone. An index on such columns may not be beneficial, especially if most of the data has the same value.

Modifying columns in the WHERE condition

Consider a query like the following:

SELECT * FROM users
WHERE LOWER(email) = 'user@example.com';

Using a function on a column can make a regular index difficult to utilize, depending on the version and configuration of the database. For cases like email searches, it is better to normalize the values when stored or design an appropriate indexing strategy.

Ignoring the queries actually run by the application

Indexes should be created based on actual access patterns, not just the table structure. Queries that seem simple in PHP code can produce different conditions after adding filters, sorting, or table relationships.

Practical steps to take now

1. Note down queries that feel slow, especially queries on list pages, searches, and reports.
2. Run EXPLAIN on those queries.
3. Check if the database is performing a full table scan or reading too many rows.
4. Compare columns in WHERE, JOIN, and ORDER BY.
5. Create small indexes that address the needs of the query, not as many indexes as possible.
6. Test again with data that closely resembles production conditions.
7. Remove indexes that are never used after sufficient observation.

What does this mean for us?

Indexes are not just optimization tricks added when a website is already slow. They are part of application design. When creating new features, consider how data will be searched, sorted, and linked.

However, indexes are also not a substitute for poor queries. Asking the application to retrieve all columns with SELECT *, loading thousands of data at once, or running queries repeatedly in loops can still be problematic. Healthy performance usually arises from a combination of sensible queries, tidy data structures, measured indexes, and testing with realistic data.

Start with one query that is truly slow. Read its execution plan, make small changes, and then measure the results. With this approach, database optimization becomes an explainable process—not just guesswork.

– Rio Yotto @rioyotto