Optimizing SQL Queries in MySQL: Small Tweaks, Big Impact

By: Roman Myskin - Sept. 14, 2025

A short practical guide with examples to help you make your MySQL queries faster and more scalable.

Why query optimization matters

Efficient SQL queries reduce I/O, lower CPU usage, and improve application responsiveness. In production systems, small improvements in query design often translate to large savings in latency and infrastructure costs.

1. Use indexes wisely

Indexes speed up lookups but add overhead on INSERT, UPDATE, and DELETE. Add them to columns used in WHERE, JOIN, and ORDER BY, but avoid over-indexing.

Tip: Prefer composite indexes that match the left-to-right column usage in queries (e.g., (customer_id, created_at) for queries filtering by customer_id then sorting by created_at).

2. Avoid SELECT *

Fetching every column increases I/O and memory usage. Select only the columns you need to reduce network transfer and speed up row reads.

-- Bad


SELECT * FROM orders WHERE status = 'paid';

-- Better
SELECT id, customer_id, total_amount, paid_at
FROM orders
WHERE status = 'paid';

3. Check your joins

Ensure join conditions use indexed columns with matching data types. Unindexed or type-mismatched joins often force full table scans and large temporary result sets.

-- Example: join on indexed keys


SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01';

4. Use EXPLAIN to understand execution plans

Run EXPLAIN <your_query> to see how MySQL executes a query — whether it uses indexes, performs full scans, or creates temporary tables. Fix the hotspots revealed by EXPLAIN.

Example: 
EXPLAIN SELECT id FROM orders WHERE customer_id = 42;

5. Limit and paginate efficiently

For large result sets, use LIMIT and indexed ordering. Prefer keyset pagination (also called cursor pagination) over OFFSET for better performance on deep pages.

-- OFFSET based (can be slow on large offset)


SELECT id, total_amount FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 1000;

-- Keyset pagination (faster)
SELECT id, total_amount FROM orders
WHERE created_at < '2025-09-01 12:00:00'
ORDER BY created_at DESC
LIMIT 50;

6. Cache results for read-heavy workloads

Frequently-run, expensive read queries can be cached using an application-level cache (Redis, Memcached) or MySQL query cache alternatives. Cache invalidation when data changes is the key challenge — design sensible TTLs or event-driven invalidation.

Quick checklist before deploying

  • Run EXPLAIN on slow queries
  • Add/remove indexes based on query patterns
  • Avoid SELECT * and unnecessary joins
  • Use keyset pagination for deep paging
  • Measure impact (latency, CPU, I/O) before/after changes

Small changes often bring large gains — a targeted index or a rewritten join can cut query time from seconds to milliseconds. Happy optimizing!



Home