Query Performance Tuning

A page that took 50 milliseconds with 100 test orders can take 5 seconds with 5 lakh real ones. Users leave, servers struggle, and the cause is almost always a handful of queries that read far more data than they need. Good tuning is not guesswork. It is a loop: measure, find the slowest queries, read their plans, fix one thing, and measure again.

This lesson builds on Indexes & EXPLAIN, where you learned what an index is and how to read basic EXPLAIN output. Here you will find slow queries on a live server, read EXPLAIN ANALYZE, write conditions that can use indexes, avoid the N+1 query trap, and keep pagination fast.

Step 1: find the slow queries

Do not tune queries at random. MySQL can record every statement that runs longer than a time limit in the slow query log. It is off by default, and the default limit, long_query_time, is 10 seconds, far too generous for a website. On a test or production server, an administrator can switch it on without a restart:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;        -- seconds; applies to new connections
SET GLOBAL log_queries_not_using_indexes = 'ON';

SHOW VARIABLES LIKE 'slow_query_log_file';

After a day of normal traffic, the file lists the slow statements with their time and the number of rows examined. The mysqldumpslow command-line tool groups similar queries and shows the worst ones first. To make the settings permanent, put them in the server's configuration file.

MySQL also keeps statistics about every statement in the Performance Schema, and the sys schema presents them in readable views:

SELECT query, exec_count, total_latency, rows_examined_avg, full_scan
FROM sys.statement_analysis
LIMIT 5;

The view lists the statements that used the most total time first. A query that takes 20 ms but runs 50,000 times a day can matter more than one that takes 3 seconds once a week.

Step 2: read the plan

EXPLAIN shows how MySQL plans to run a query. EXPLAIN ANALYZE (MySQL 8.0.18 and later) actually runs it and reports what really happened, step by step, in a tree format. Read it from the most indented line outwards:

EXPLAIN ANALYZE
SELECT order_id, order_date FROM orders WHERE status = 'pending';

A typical result looks like this; your numbers will differ:

-> Filter: (orders.`status` = 'pending')  (cost=1.75 rows=1.5) (actual time=0.041..0.048 rows=2 loops=1)
    -> Table scan on orders  (cost=1.75 rows=15) (actual time=0.035..0.044 rows=15 loops=1)

Two signs of trouble: a table scan on a big table, and a large gap between estimated and actual rows. The second means MySQL's statistics are out of date, and ANALYZE TABLE orders; refreshes them. Because EXPLAIN ANALYZE really runs the statement, be careful with slow queries on a busy server. EXPLAIN FORMAT=TREE shows the same tree without running anything.

Step 3: index what the query uses

Indexes help three parts of a query:

• WHERE: columns you filter on, such as status or customer_id.
• JOIN ... ON: the column on the "many" side of the join, such as orders.customer_id. InnoDB indexes foreign key columns automatically, but tables created without foreign keys often lack them.
• ORDER BY: if the index is already sorted the way you want, MySQL skips the sort. In EXPLAIN, Using filesort means an extra sorting step.

A composite index can serve all three at once. For "a customer's orders, newest first":

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

SELECT order_id, order_date
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;

MySQL jumps to customer 1 in the index, where the dates are already in order, and reads them backwards. If the index contains every column the query needs, MySQL never touches the table itself. EXPLAIN then shows Using index, and the index is called a covering index. InnoDB secondary indexes always include the primary key, so order_id comes for free here.

Step 4: write conditions the index can use

An index stores raw column values in sorted order. A condition that changes the column before comparing it cannot use that order, so MySQL checks every row. The fix is to move the work to the other side of the comparison. These two queries return the same products, but only the second could use an index on price:

SELECT product_name, price FROM products WHERE price * 1.18 > 5000 ORDER BY price;

SELECT product_name, price FROM products WHERE price > 5000 / 1.18 ORDER BY price;

product_name                | price
----------------------------+------
Graphics Tablet             | 4599
1TB External SSD            | 6499
Noise-Cancelling Headphones | 8999
27-inch Monitor             | 15999
(4 rows)

product_name                | price
----------------------------+------
Graphics Tablet             | 4599
1TB External SSD            | 6499
Noise-Cancelling Headphones | 8999
27-inch Monitor             | 15999
(4 rows)

The same rule covers other common slow patterns:

When you truly need to search by an expression, MySQL 8.0.13 and later can index the expression itself, called a functional index: CREATE INDEX idx_orders_year ON orders ((YEAR(order_date)));. Note the double brackets, and that the query must use exactly the same expression.

Step 5: fetch only what you need

SELECT * reads and sends every column, including long text you never display. It also stops MySQL from using a covering index and can break code when someone adds a column. List the columns the page needs. Likewise, let the database do the counting and summing: fetching 10,000 rows into PHP to add them up is far slower than SELECT SUM(...).

The worst version of "fetching too much" is the N+1 query problem. A page shows the latest five orders, then, inside a loop, runs one more query per order to fetch the customer's name:

$orders = $pdo->query('SELECT order_id, customer_id FROM orders ORDER BY order_date DESC LIMIT 5')->fetchAll();
foreach ($orders as $order) {   // 1 query above + 1 query per order = N + 1 queries
    $stmt = $pdo->prepare('SELECT first_name, city FROM customers WHERE customer_id = ?');
    $stmt->execute([$order['customer_id']]);
    $customer = $stmt->fetch();
}

Six small queries do not sound bad, but a list of 100 orders means 101 round trips to the database on every page view. Frameworks with an ORM, such as Laravel's Eloquent, create this pattern easily when you access a related record inside a loop. One JOIN gets everything in a single trip:

SELECT o.order_id, o.order_date, c.first_name, c.city
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC
LIMIT 5;

order_id | order_date | first_name | city
---------+------------+------------+----------
115      | 2025-05-20 | Aarav      | Rohtak
114      | 2025-05-11 | Farhan     | Lucknow
113      | 2025-05-02 | Rohan      | Mumbai
112      | 2025-04-18 | Isha       | Gurugram
111      | 2025-04-10 | Arjun      | Hyderabad
(5 rows)

When a join is not possible, collect the IDs first and fetch all the related rows with one WHERE customer_id IN (...) query.

Step 6: keep pagination cheap

LIMIT 20 OFFSET 100000 makes MySQL read and throw away 100,000 rows before returning 20, as LIMIT & OFFSET explained. Keyset pagination remembers where the last page ended and continues from there. When the sort column can have ties, such as two orders on the same date, add the primary key as a tie-breaker. If the previous page ended with order 111 on 10 April:

SELECT order_id, order_date, status
FROM orders
WHERE order_date < '2025-04-10'
   OR (order_date = '2025-04-10' AND order_id < 111)
ORDER BY order_date DESC, order_id DESC
LIMIT 4;

order_id | order_date | status
---------+------------+----------
110      | 2025-04-01 | delivered
109      | 2025-03-22 | delivered
108      | 2025-03-15 | shipped
107      | 2025-03-09 | delivered
(4 rows)

With an index on (order_date, order_id), every page is as fast as the first. Also question the "Page 1 of 25,000" line: SELECT COUNT(*) on a huge InnoDB table must count through an index every time. Many sites show "Next" without a total, or cache the count.

Common mistakes

• Tuning without measuring. Find the queries that use the most total time first, and time them before and after each change.
• Adding an index for every column. Each index slows down writes. Add indexes for real queries and check that EXPLAIN uses them.
• Testing on tiny data. With 15 rows, every plan is fast. Test with realistic row counts.
• Hiding columns inside functions. DATE(created_at) = '2025-05-20' scans the table; use a range.

Key points

• Measure first: the slow query log and sys.statement_analysis show which queries cost the most.
• EXPLAIN ANALYZE runs the query and shows actual rows and times per step; compare them with the estimates.
• Index the columns used in WHERE, JOIN ... ON and ORDER BY; a covering index avoids reading the table.
• Keep indexed columns bare in conditions; move calculations to the other side, or use a functional index.
• Select only the columns you need, and replace N+1 loops with one JOIN or IN query.
• Use keyset pagination with a tie-breaker for deep pages.

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2025-04-01'
  AND order_date < '2025-05-01'
ORDER BY order_date;