Categories
Database MySQL Performance

Optimizing MySQL Query Performance

Introduction

Your web application is slow. Pages take seconds to load. Users are complaining. You check your code – it's fine. You check your server – plenty of resources. The problem? Your database queries.

Database performance is often the bottleneck in web applications. A poorly optimized query can turn a fast application into a sluggish mess. But with the right techniques, you can make your database scream.

Let's learn how to optimize MySQL queries and make your applications fast.

Identifying Slow Queries

First, find the problem queries. MySQL provides tools:

Enable the slow query log:

In my.cnf:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2

This logs queries taking longer than 2 seconds.

Analyze the log:

mysqldumpslow /var/log/mysql/slow-queries.log

This shows you which queries are slow and how often they run.

The EXPLAIN Command

EXPLAIN shows how MySQL executes a query:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Output:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Key columns:

  • type: How MySQL accesses the table (ALL = table scan = bad)
  • possible_keys: Indexes MySQL could use
  • key: Index MySQL actually used
  • rows: Estimated rows to examine

Type values (best to worst):

  • const: Single row match (primary key lookup)
  • eq_ref: One row from this table for each row from previous table
  • ref: Multiple rows match an index
  • range: Index used with range (>, <, BETWEEN)
  • index: Full index scan (better than ALL, but still slow)
  • ALL: Full table scan (slowest)

If you see type: ALL and many rows, you have a problem.

Indexes: The Key to Speed

Indexes are like a book's index – they let MySQL find data without scanning every row.

Without index:

SELECT * FROM users WHERE email = '[email protected]';
-- MySQL scans all 1,000,000 rows

With index:

CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = '[email protected]';
-- MySQL finds the row instantly

Creating indexes:

-- Single column index
CREATE INDEX idx_email ON users(email);

-- Multiple column index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

When to index:

  • Columns in WHERE clauses
  • Columns in JOIN conditions
  • Columns in ORDER BY
  • Columns in GROUP BY

When NOT to index:

  • Small tables (< 1000 rows)
  • Columns rarely used in queries
  • Columns with low selectivity (many duplicate values)
  • Columns that change frequently

Indexes speed up reads but slow down writes. Balance is important.

Index Order Matters

For composite indexes, order matters:

CREATE INDEX idx_name ON users(last_name, first_name);

This index helps:

-- Uses index
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

-- Doesn't use index
SELECT * FROM users WHERE first_name = 'John';

MySQL can use the index from left to right. If you skip the first column, the index isn't used.

Rule: Put the most selective column first (the one that narrows results most).

The SELECT * Problem

Don't select columns you don't need:

Bad:

SELECT * FROM users WHERE id = 123;

Good:

SELECT id, name, email FROM users WHERE id = 123;

Why? MySQL has to retrieve and transfer all columns. This is slower and uses more memory.

Also, SELECT * prevents using "covering indexes" (indexes that contain all needed columns).

Covering Indexes

A covering index contains all columns needed for a query:

-- Create index with all needed columns
CREATE INDEX idx_user_info ON users(id, name, email);

-- This query uses only the index (faster)
SELECT id, name, email FROM users WHERE id = 123;

MySQL doesn't need to read the table – the index has everything.

JOIN Optimization

JOINs can be slow. Optimize them:

Index JOIN columns:

-- Slow without indexes
SELECT users.name, posts.title
FROM users
JOIN posts ON users.id = posts.user_id;

-- Fast with indexes
CREATE INDEX idx_user_id ON posts(user_id);

Small tables first: MySQL JOINs from left to right. Put smaller tables first when possible.

Use INNER JOIN, not WHERE:

-- Bad (implicit join)
SELECT users.name, posts.title
FROM users, posts
WHERE users.id = posts.user_id;

-- Good (explicit join)
SELECT users.name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;

Explicit JOINs are clearer and sometimes optimized better.

Limit Results

Always limit results when possible:

-- Returns millions of rows
SELECT * FROM logs;

-- Returns only what you need
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

LIMIT reduces memory usage and network transfer.

Subqueries vs JOINs

Subqueries can be slow. Sometimes JOINs are faster:

Subquery (potentially slow):

SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts WHERE published = 1);

JOIN (often faster):

SELECT DISTINCT users.*
FROM users
INNER JOIN posts ON users.id = posts.user_id
WHERE posts.published = 1;

Test both. Sometimes subqueries are optimized well, sometimes not.

COUNT(*) Optimization

COUNT(*) can be slow on large tables:

Slow:

SELECT COUNT(*) FROM users;
-- Scans entire table

Solutions:

  1. Cache the count: Store counts in a separate table, update via triggers
  2. Approximate count: sql SHOW TABLE STATUS LIKE 'users'; The Rows column is approximate but instant
  3. Add WHERE clause: sql SELECT COUNT(*) FROM users WHERE created_at > '2009-01-01'; With index on created_at, this is faster

Avoid Functions in WHERE

Don't use functions on indexed columns in WHERE:

Bad (index not used):

SELECT * FROM users WHERE YEAR(created_at) = 2009;

Good (index used):

SELECT * FROM users WHERE created_at BETWEEN '2009-01-01' AND '2009-12-31';

Functions prevent index usage. Rewrite queries to avoid them.

Query Cache

MySQL caches query results:

Check cache status:

SHOW VARIABLES LIKE 'query_cache%';

Enable query cache: In my.cnf:

query_cache_type = 1
query_cache_size = 64M

Identical queries return cached results instantly.

Limitations:

  • Any table update invalidates cache for that table
  • Only helps with repeated identical queries
  • Can hurt performance on write-heavy applications

Pagination Done Right

Paginating large result sets:

Bad:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 100000, 20;

MySQL still examines 100,020 rows, discards first 100,000.

Better:

SELECT * FROM posts
WHERE created_at < '2009-07-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

Use a where clause based on the last seen value.

Best:

SELECT * FROM posts
WHERE id < 12345
ORDER BY id DESC
LIMIT 20;

If IDs are sequential, use ID-based pagination.

UNION vs UNION ALL

UNION removes duplicates, UNION ALL doesn't:

Slower:

SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM admins WHERE active = 1;

Faster:

SELECT name FROM users WHERE active = 1
UNION ALL
SELECT name FROM admins WHERE active = 1;

If you know there are no duplicates, use UNION ALL.

Analyzing Index Usage

See which indexes are actually used:

SHOW INDEX FROM users;

Check Cardinality – high cardinality means selective index (good).

Find unused indexes:

SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
AND INDEX_NAME != 'PRIMARY'
ORDER BY TABLE_NAME, INDEX_NAME;

Then check slow query log to see if these indexes are used. Drop unused indexes.

Table Design Matters

Normalize appropriately: Avoid redundant data, but don't over-normalize.

Use appropriate data types:

-- Bad (wastes space)
email VARCHAR(255)

-- Good
email VARCHAR(100)

-- Bad (VARCHAR for small, fixed values)
status VARCHAR(20)

-- Good (ENUM for fixed values)
status ENUM('active', 'inactive', 'pending')

Smaller data types = faster queries and less disk space.

Use NOT NULL when possible:

-- Slightly slower (allows NULL)
name VARCHAR(100)

-- Slightly faster
name VARCHAR(100) NOT NULL

NOT NULL columns are marginally faster.

Query Optimization Workflow

  1. Identify slow queries (slow query log)
  2. EXPLAIN the query (understand execution plan)
  3. Add indexes (on WHERE, JOIN, ORDER BY columns)
  4. Rewrite query (avoid subqueries, functions in WHERE)
  5. Test (verify improvement with EXPLAIN)
  6. Monitor (watch for regression)

Common Mistakes

Too many indexes: Each index slows down writes. Don't index everything.

Wrong index order: Column order in composite indexes matters.

*Using SELECT : Only select needed columns.

Using LIKE '%search%': Leading wildcard prevents index usage. Use full-text search instead.

Not using LIMIT: Always limit results when possible.

Ignoring EXPLAIN: Always check execution plans.

Practical Example

Let's optimize a real query:

Original (slow):

SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2009-01-01'
GROUP BY u.id
ORDER BY post_count DESC;

EXPLAIN shows: Table scan on users, no index on posts.user_id

Optimizations:

-- Add indexes
CREATE INDEX idx_created_at ON users(created_at);
CREATE INDEX idx_user_id ON posts(user_id);

-- Rewrite query
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2009-01-01'
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 100;

Changes:

  • Added index on users.created_at
  • Added index on posts.user_id
  • Added LIMIT (probably don't need all results)
  • Explicitly selected columns

Result: 100x faster.

Tools and Resources

MySQL Query Profiler:

SET profiling = 1;
SELECT * FROM users WHERE email = '[email protected]';
SHOW PROFILE;

mysqldumpslow: Analyze slow query log

EXPLAIN: Understand query execution

Books:

  • "High Performance MySQL" by Baron Schwartz

Conclusion

Database optimization is crucial for web application performance. Slow queries kill user experience and waste server resources.

The key techniques:

  • Use EXPLAIN to understand queries
  • Add indexes strategically
  • Avoid SELECT *
  • Optimize JOINs
  • Rewrite problematic queries
  • Monitor with slow query log

Start with the slow query log. Find the slowest queries. Use EXPLAIN. Add indexes. Rewrite if needed. Test. Repeat.

Most performance problems come from a few bad queries. Fix those and your application will be dramatically faster.

Don't guess – measure. Use EXPLAIN, use profiling, use the slow query log. Data-driven optimization works.

Your users will notice the difference. Fast applications feel better, convert better, and retain users better.

Optimize your queries. Your users (and your servers) will thank you.

By Shishir Sharma

Shishir Sharma is a Software Engineering Leader, husband, and father based in Ottawa, Canada. A hacker and biker at heart, and has built a career as a visionary mentor and relentless problem solver.

With a leadership pedigree that includes LinkedIn, Shopify, and Zoom, Shishir excels at scaling high-impact teams and systems. He possesses a native-level mastery of JavaScript, Ruby, Python, PHP, and C/C++, moving seamlessly between modern web stacks and low-level architecture.

A dedicated member of the tech community, he serves as a moderator at LUG-Jaipur. When he’s not leading engineering teams or exploring new technologies, you’ll find him on the open road on his bike, catching an action movie, or immersed in high-stakes FPS games.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.