Query Optimization

Introduction

Two queries can ask for the exact same data and return the exact same result, yet one finishes in a few milliseconds while the other takes seconds. The difference is almost always in how the query is written — whether it gives MySQL a chance to use its indexes efficiently, or forces it to work far harder than necessary.

This lesson covers practical, everyday habits for writing efficient queries. None of these require exotic features — just an awareness of how MySQL actually executes what you write.

Writing Index-Friendly WHERE Clauses

For MySQL to use an index on a column, the WHERE clause generally needs to compare that column directly against a value, without transforming the column first. A plain, direct comparison lets MySQL jump straight to the matching rows using the index.

-- Assuming an index exists on customer_id
SELECT order_id, total
FROM orders
WHERE customer_id = 42;

+----------+--------+
| order_id | total  |
+----------+--------+
| 1042     | 59.99  |
| 1187     | 120.00 |
+----------+--------+

Avoid Wrapping Indexed Columns in Functions

It is tempting to write something like WHERE YEAR(order_date) = 2024 to find all orders from a given year. The problem is that MySQL must calculate YEAR(order_date) for every single row before it can compare it — the index on order_date cannot be used, because the index stores the raw date values, not the result of a function applied to them.

SELECT * FROM orders
WHERE YEAR(order_date) = 2024;

SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Avoiding Unnecessary Joins

Every JOIN gives MySQL more work: it must match rows between tables, which costs time and memory, especially on large tables. If a query joins a table but never actually uses any column from it — not in the SELECT list, not in the WHERE clause — that join is pure overhead that can simply be removed.

SELECT o.order_id, o.total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT o.order_id, o.total
FROM orders o;

Using LIMIT

If your application only ever displays the first 10 results — a page of search results, a "recent orders" widget — asking MySQL for every matching row and then discarding all but 10 in your application code wastes both database and network effort. LIMIT tells MySQL to stop as soon as it has enough rows.

SELECT order_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Wildcard LIKE Searches

The LIKE operator with a wildcard on the right, like 'text%', can still use an index — MySQL can jump to the first row starting with "text" and read forward. A leading wildcard, like '%text%' or '%text', cannot use a normal index at all, because a matching value could start with anything; MySQL has no choice but to scan every row and check each one.

EXISTS vs IN

For correlated subqueries — where the inner query refers back to the outer query's row — EXISTS can outperform IN, because EXISTS can stop as soon as it finds a single matching row, while IN, in some cases, may need to build out the full list of inner results first.

-- Find customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

This is a subtle, workload-dependent difference rather than a universal rule — MySQL's optimizer has improved significantly at handling IN efficiently too — but for correlated existence checks, EXISTS is a reasonable default to reach for.

Common Mistakes

Key Takeaways

Summary

Writing efficient queries is mostly about giving MySQL's optimizer every chance to use the indexes and shortcuts already available to it, instead of accidentally working against them.

In this lesson, you learned how to write index-friendly WHERE clauses, avoid unnecessary joins, use LIMIT, handle wildcard searches carefully, and choose between EXISTS and IN. Next, you will learn to use the EXPLAIN statement to actually see how MySQL executes your queries, so you can confirm these techniques are working as expected.