MySQL Index Optimization: Fixing Slow Queries

Used correctly, a MySQL index turns a query that takes seconds into one that takes milliseconds; used carelessly, it does nothing at all and can even slow down your writes. On game servers in particular, tables for sessions, logs, inventory and rankings quickly grow into millions of rows. At that point you need to measure the bottleneck instead of guessing it. In this guide we will walk through diagnosing slow queries with EXPLAIN, deciding which columns to index, and avoiding the most common mistakes.

Why indexes work

In a table without an index, MySQL scans the whole table from start to finish to find the rows you want (a full table scan). As the table grows, this gets linearly slower. An index is, in most cases, a B-tree structure: it keeps the values of the indexed column sorted and lets MySQL jump straight to the relevant block using binary-search-like logic, even across millions of rows.

This comes at a cost: every INSERT, UPDATE and DELETE also has to update the index. So an index speeds up reads while slowing writes a little and consuming disk space. That is why the goal is not "index everything" but to index the right columns.

Finding the slow query: the slow query log

First you need to learn which query is actually slow. MySQL's slow query log records every query that runs longer than a threshold you set. You can enable it quickly per session:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- queries longer than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

After it has run for a while, summarize the most frequent and slowest queries with mysqldumpslow:

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

The point here is to find a concrete query to optimize. Work with real traffic, not guesses.

Diagnosing with EXPLAIN

Once you have a slow query, use EXPLAIN to see how MySQL executes it. A sample game-server query:

EXPLAIN SELECT * FROM player_logs
WHERE account_id = 4815 AND action = 'login'
ORDER BY created_at DESC
LIMIT 20;

In the output, focus on these columns:

• type: the access method. ALL is bad (full scan), ref and range are good, const/eq_ref are best.
• key: the index MySQL actually used. NULL means no index was used at all.
• rows: the estimated number of rows MySQL expects to scan. Lower is better.
• Extra: if you see Using filesort or Using temporary, extra work is being done for sorting or grouping.

For a more detailed plan, EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and shows measured timings — you see the real cost instead of an estimate.

Designing the right index

The query above filters by account_id and action and sorts by created_at. The ideal index is a composite index that mirrors exactly that order:

CREATE INDEX idx_logs_acc_action_time
  ON player_logs (account_id, action, created_at);

The key concept here is the leftmost prefix rule. If a composite index is (a, b, c), MySQL can use it for lookups on a, a+b or a+b+c, but not for b or c alone. That is why column order matters:

• Equality (=) filters come first (here account_id, action).
• Then the range and ORDER BY column comes (created_at).

With this ordering, MySQL handles both filtering and sorting through the index, and Using filesort disappears.

Common mistakes

• Wrapping a column in a function: writing WHERE DATE(created_at) = '2026-06-27' prevents the index from being used. Use a range instead: WHERE created_at >= '2026-06-27' AND created_at < '2026-06-28'.
• Leading wildcard: LIKE '%abc' disables the index, while LIKE 'abc%' can use it.
• Type mismatch: comparing a numeric column with a string like WHERE id = '123' can trigger an implicit conversion and a full scan.
• Over-indexing: indexing rarely queried or low-selectivity columns (e.g. a flag with only 0/1 values) on their own usually helps nothing.
• Needless SELECT *: if you select only the columns you need, sometimes the index itself satisfies the whole query (a covering index) and the table is never touched.

Verify and maintain

After adding an index, run the same EXPLAIN again: type should move from ALL to ref/range, rows should drop sharply, and filesort should disappear. To keep statistics fresh, run ANALYZE TABLE player_logs; from time to time. You can find and drop unused indexes through the sys.schema_unused_indexes view to reduce write overhead.

Remember: index optimization is not a one-off task but a continuous loop — measure, index, verify, repeat.

Frequently Asked Questions

How many indexes can I add to a table?

While the technical limit is high, the practical limit depends on your workload. On write-heavy tables, keep indexes few and precise; on read-heavy reporting tables, more indexes can be reasonable. Remember that every index carries a write and storage cost.

Are separate indexes or a composite index better?

If a query filters several columns together, one correctly ordered composite index is almost always better than separate single-column indexes. In most cases MySQL uses a single index per query most efficiently.

Is the primary key already an index?

Yes. In InnoDB the primary key is also the clustered index, and the row data is physically stored in its order. That is why choosing a short, sequential, immutable primary key (such as an auto-increment) matters for performance.

Stop guessing when your database slows down. Game server or web app, it does not matter — let's measure your slow queries together and build the right index strategy. Get in touch and let's speed up your database.