Top 10 MySQL Query Optimization Techniques to Boost Performance

Quick answer

To make a MySQL query faster, do these things in order:

• Measure first. Find the slow query, then run EXPLAIN on it.
• Fetch less. Select only the columns and rows you need.
• Help MySQL use your indexes. Do not wrap indexed columns in functions.
• Add the right index. Use composite and covering indexes for your most common queries.
• Fix the joins. Match data types and collations on both sides.

The rest of this guide shows each step with a bad query, a good query, and what to check.

The examples use two simple tables: users and orders.

1. Find slow queries before you change anything

Do not guess. Find which queries are actually slow.

Turn on the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

This logs any query that takes longer than 1 second. For a busy site, you can lower this number later.

Tip: The slow query log shows you what is slow. EXPLAIN shows you why.

2. Read the plan with EXPLAIN and EXPLAIN ANALYZE

Put EXPLAIN in front of your query:

EXPLAIN SELECT id, email FROM users WHERE email = 'a@example.com';

What to look at:

• type: If it says ALL, MySQL is reading the whole table. This is the most common cause of slow queries.
• key: The index MySQL actually used. If it is NULL, no index was used.
• rows: MySQL’s estimate of how many rows it will read. Lower is better.

EXPLAIN ANALYZE (available from MySQL 8.0.18) runs the query and shows real time and real row counts, not just estimates:

EXPLAIN ANALYZE SELECT id, email FROM users WHERE email = 'a@example.com';

Use it when the estimate and the real speed do not match. It runs the query for real, so be careful with slow queries on a live server.

3. Select only the columns you need

Bad:

SELECT * FROM users;

Good:

SELECT id, email FROM users LIMIT 20;

Why it matters:

• Fewer columns means less data to read, send and store in memory.
• SELECT * also stops MySQL from using a covering index (see technique 7).
• LIMIT stops MySQL from sending thousands of rows when your page shows 20.

4. Write queries that can use your index

If you wrap an indexed column in a function, MySQL usually cannot use the index. It has to check every row.

Bad:

SELECT id FROM orders WHERE YEAR(created_at) = 2026;

Good:

SELECT id FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

The good version leaves created_at alone, so MySQL can jump straight to the right rows in the index.

Other common mistakes:

• WHERE price + 10 > 100 (do the math on the other side: WHERE price > 90)
• WHERE LOWER(email) = 'a@example.com' (store emails in a consistent case, or use a case-insensitive collation)
• WHERE name LIKE '%smith' (a leading wildcard cannot use a normal index)

5. Add indexes where you filter, join and sort

Create indexes on columns you often use in WHERE, JOIN, ORDER BY and GROUP BY.

CREATE INDEX idx_orders_user_id ON orders (user_id);

Check it worked: run EXPLAIN again. The key column should now show your new index, and rows should drop.

For more depth, read our guide on Advanced Indexing Techniques in MySQL.

6. Use composite indexes in the right order

If a query filters on two columns, one index on both columns is usually better than two separate indexes.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

The left-to-right rule: MySQL can use this index for:

• WHERE user_id = 5
• WHERE user_id = 5 AND status = 'paid'

But it cannot use it well for WHERE status = 'paid' alone, because user_id (the first column) is missing.

How to pick the order:

1. Columns you compare with = go first.
2. The column you use for a range (>, <, BETWEEN) goes last.

Technique 9 explains why.

7. Use covering indexes

A covering index holds every column the query needs. MySQL reads the answer from the index and never touches the table.

CREATE INDEX idx_orders_user_status_total ON orders (user_id, status, total);

SELECT status, total FROM orders WHERE user_id = 5;

How to check: run EXPLAIN. If the Extra column shows Using index, the query is covered.

Warning: Do not add every column to every index. Bigger indexes cost more space and slow down writes. Use this for your most frequent and most important queries only.

8. Fix slow IN clause and WHERE IN queries

This is one of the most searched MySQL problems, and most guides skip it.

IN works well when the column is indexed and the list is short:

SELECT id, total FROM orders WHERE user_id IN (3, 8, 15);

It gets slow when:

• The column has no index. Add one first.
• The list is very long (thousands of values). Sending a huge list adds parsing work and can lead to poor plans.
• The list comes from a subquery and the plan is poor.

Fix for a long list: put the values in a temporary table and join.

CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (3), (8), (15);

SELECT o.id, o.total
FROM orders o
JOIN tmp_ids t ON t.id = o.user_id;

Fix for a subquery: try EXISTS or a JOIN, then compare with EXPLAIN.

SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);

Be honest with yourself: newer MySQL versions optimise many subqueries well. Always test both versions with EXPLAIN instead of assuming a JOIN is faster.

9. Speed up range queries

Range queries use >, <, BETWEEN or >=.

SELECT id, total FROM orders
WHERE user_id = 5
  AND created_at BETWEEN '2026-01-01' AND '2026-03-31';

The key rule: in a composite index, MySQL stops using columns after the first range column to narrow the search.

So for the query above:

• Good index: (user_id, created_at). Equal column first, range column last.
• Weaker index: (created_at, user_id). The range comes first, so user_id cannot help narrow the search as well.

Also check:

• Do not put a function on the range column (see technique 4).
• For big date ranges that return most of the table, an index may not help. MySQL may correctly choose a full scan.

10. Fix slow pagination

This is slow on big tables:

SELECT id, total FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

MySQL has to read and throw away 100,000 rows first.

Better: remember the last ID you showed and continue from there.

SELECT id, total FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

This is called keyset pagination. It stays fast on page 1 and on page 5,000.

Common myths

• “Use the query cache.” The query cache was removed in MySQL 8.0. If an old guide tells you to enable it, the guide is out of date.
• “Always use JOIN instead of subqueries.” Not always. Test both with EXPLAIN.
• “More indexes are always better.” Every index slows down INSERT, UPDATE and DELETE, and uses disk and memory. Remove indexes that no query uses.

Check your joins and data types

If you join two tables, the joined columns should have the same data type, character set and collation. Joining an INT to a VARCHAR, or utf8mb4 to a different collation, can stop MySQL from using the index and force slow conversions.

Check with:

SHOW CREATE TABLE orders;
SHOW CREATE TABLE users;

Compare the join columns side by side.

Use the smallest data type that fits

Smaller columns mean smaller rows and smaller indexes. More of your data then fits in memory (the InnoDB buffer pool), so MySQL reads less from disk.

Bad:

CREATE TABLE orders (
  id INT,
  status INT,
  note TEXT
);

Good:

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  status TINYINT UNSIGNED NOT NULL,
  note VARCHAR(255)
);

Rules of thumb:

• Use TINYINT for small status codes (0 to 255 when unsigned) instead of INT.
• Use VARCHAR with a sensible length instead of TEXT when the length is predictable.
• Use UNSIGNED when a number can never be negative.
• Do not shrink a type if the value may grow later. Changing a column type on a big table rebuilds it, which can be slow.

Keep table statistics fresh with ANALYZE TABLE

MySQL picks a plan using statistics about your data. If the data changed a lot (a big import or delete), those statistics can be stale and MySQL may pick a poor plan.

ANALYZE TABLE orders;

Run it, then run EXPLAIN on your slow query again. On a busy table, run it at a quiet time.

About OPTIMIZE TABLE: it is not a routine job. On InnoDB it rebuilds the whole table, which can be slow and can block work. Use it rarely, for example after deleting a large part of a table, and run it off-peak.

Do I need an online MySQL query optimizer tool?

Many people search for a tool that rewrites a query for them. No tool replaces EXPLAIN, but these help you find the problem faster:

• MySQL Workbench: shows a visual explain plan.
• Percona Toolkit (pt-query-digest): reads your slow query log and ranks the worst queries.
• Performance Schema and sys schema: built into MySQL, they show which statements use the most time.

When your table is truly huge

If queries are still slow after indexing and rewriting, and the table has hundreds of millions of rows, look at partitioning. See Partitioning in MySQL for how it works and when it does not help.

FAQ

Q. How do I make a MySQL query faster?

A. Find the slow query, run EXPLAIN, then fix the biggest problem it shows. Usually that is a missing index, a function on an indexed column, or selecting too much data.

Q. How do I speed up a MySQL query that uses IN?

A. Make sure the column is indexed. If the list is very long, load the values into a temporary table and use a JOIN. Check with EXPLAIN that MySQL is using your index.

Q. What is the best index for a range query?

A. Put the columns you compare with = first, and the range column last.

Q. What does type ALL mean in EXPLAIN?

A. It means MySQL reads every row in the table. This is usually the first thing to fix.

Q. Does the MySQL query cache still exist?

A. No. It was removed in MySQL 8.0.

Q. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

A. EXPLAIN shows the plan and estimates. EXPLAIN ANALYZE runs the query and shows real time and row counts. It is available from MySQL 8.0.18.