The role of indexes and the query patterns that make MySQL slow

MySQL is not always slow because the server is weak. In many cases, the real problem is the query design, the amount of data being fetched, or the lack of proper indexes.

A query that touches a few dozen rows is very different from a query that scans hundreds of thousands of rows before finding the final result.

Quick conclusion

If you only remember four things, keep these:

1. an index helps MySQL jump closer to the data it needs instead of scanning the whole table
2. a query without the right index often falls into a full table scan
3. SELECT * or fetching too many rows and filtering them in code slows down both MySQL and the application
4. MySQL optimization is not only about adding indexes, but also about selecting the right columns and the right number of rows

What is the role of an index in MySQL?

An index is like a table of contents in a book.

• without it, you flip through page after page
• with it, you jump much closer to the section you want

The same idea applies to MySQL:

• without a suitable index, the database may scan a large part of the table
• with a suitable index, it can narrow down the target data much faster

Indexes are especially important for columns used in:

• WHERE
• JOIN
• ORDER BY
• GROUP BY

Example:

SELECT id, name, email
FROM users
WHERE email = '[email protected]';

If email has no index, MySQL may need to scan a large number of rows.
If email is indexed, the lookup is much faster.

When does a missing index slow MySQL down the most?

The impact is usually obvious when:

• the table is already large
• the query runs frequently
• the query has a clear filter but MySQL still scans many rows
• sorting or joins happen on columns without a suitable index

Example:

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

Without a suitable index, MySQL may:

• scan many rows to find status = 'paid'
• then sort them again by created_at

A better index may look like:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

The key point is that having an index is not enough by itself.
Column order inside a composite index matters.

Slow queries caused by selecting too many columns

This is a very common mistake:

SELECT *
FROM users
WHERE status = 'active';

Problems with SELECT *:

• more data is read than necessary
• more data is transferred from MySQL to the app
• more memory is used in the app
• large text or JSON columns may be fetched even when unused

If the screen only needs id, name, and email, be explicit:

SELECT id, name, email
FROM users
WHERE status = 'active';

On large tables or high-traffic systems, this difference is very real.

Slow queries caused by fetching extra rows and filtering in code

This is one of the most common bad optimization patterns.

Example:

SELECT id, user_id, status, total, created_at
FROM orders
WHERE created_at >= '2026-06-01';

Then the backend does:

const paidOrders = orders.filter((item) => item.status === 'paid').slice(0, 20);

Problems:

• the database returns more rows than needed
• the app spends RAM holding the data
• the app spends CPU filtering again
• the network between app and DB is wasted too

A better approach is to push the filter back to MySQL:

SELECT id, user_id, total, created_at
FROM orders
WHERE created_at >= '2026-06-01'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

A practical rule:

• filter in the database
• limit rows in the database
• select only the columns you really need

Do not fetch 5000 rows and reduce them to 20 in application code if SQL can do that directly.

Slow queries caused by using an index the wrong way

Having an index does not guarantee speed if the query shape makes it hard for MySQL to use that index.

Common examples:

Applying a function to a filtered column

SELECT id, created_at
FROM orders
WHERE DATE(created_at) = '2026-06-16';

This can make MySQL less likely to use the index on created_at.

Prefer:

SELECT id, created_at
FROM orders
WHERE created_at >= '2026-06-16 00:00:00'
  AND created_at < '2026-06-17 00:00:00';

Using LIKE with a leading wildcard

SELECT id, name
FROM products
WHERE name LIKE '%iphone%';

This often prevents a regular BTREE index from helping the way you expect.

Depending on the use case, you may need:

• a full-text index
• a dedicated search engine
• or a different search flow

Joining on a column without an index

SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';

If orders.user_id is not indexed appropriately, this join can become slow once data grows.

Should you add indexes everywhere?

No.

Indexes make reads faster, but they also cost:

• more storage space
• slower insert/update/delete because indexes also need updates
• too many indexes can make tuning more confusing

So the mindset should not be:

• query is slow -> add random indexes

A better mindset:

• which queries are important
• which columns are actually used for filter/join/order
• whether the current indexes already cover them
• whether a new index serves a real repeated query

How should you inspect slow queries?

At minimum, use:

• EXPLAIN
• slow query log
• a direct review of how many columns and rows the query pulls

Example:

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

If you see signs like:

• type: ALL
• a very high scanned row count
• the expected index is not being used

then the query or index design needs another look.

The real-world patterns that often make MySQL slow

These are very common in production backends:

1. filtering on a column without an index
2. joining on a column without an index
3. using SELECT * when only a few columns are needed
4. fetching thousands of rows and filtering/sorting/paginating in code
5. applying functions like DATE() or LOWER() directly to filtered columns
6. sorting on a large column set without a suitable index
7. using deep pagination with very large OFFSET

Among these, the two most common and wasteful issues are usually:

• missing the right index
• fetching extra data and processing it later in application code

When should you rewrite the query first, and when should you add an index first?

If the query is fetching too much unnecessary data, rewrite the query first.

For example:

• remove SELECT *
• add LIMIT
• push filters down into SQL
• remove filtering logic from the app when the database can do it

If the query is already shaped well but still scans too many rows, then inspect:

• whether an index is missing
• whether the column order inside the index is correct
• whether the join/order/filter pattern matches the index design

A short checklist to avoid slowing MySQL down

Before shipping an important query, check:

1. Does it use SELECT *?
2. Does it fetch more rows than the feature really needs?
3. Does it filter, sort, or paginate in code instead of SQL?
4. Do the columns in WHERE, JOIN, and ORDER BY have suitable indexes?
5. Does it apply functions that make indexes harder to use?
6. Have you run EXPLAIN?

Conclusion

The biggest role of an index is to reduce how much data MySQL needs to scan before finding the result you want. But adding indexes alone is not enough.

Many systems are slow because of very basic mistakes:

• missing indexes
• selecting too many columns
• fetching too many rows
• pulling extra data and only then processing it in code

If you want practical MySQL performance improvements, start with three questions:

1. Is this query selecting exactly the data it needs?
2. Is the database scanning too many rows?
3. Do the current indexes actually support this query?

Getting those three things right already speeds up a large share of real-world queries without touching infrastructure.