Database Query Optimization: Indexes, N+1 Queries and Slow Logs

A page that takes four seconds to load usually has one query that takes three of them, or three hundred small queries where two would do. Database query optimization on MySQL and MariaDB is mostly the work of finding out which of those you have, and that takes a log file and one command, not a larger server.

Everything here applies to any PHP application on either database, with Laravel used for the ORM examples. Work in this order: capture the slow queries, read how the server runs them, fix the index or the query, then deal with query count and pagination.

Let the slow query log choose your targets

The slow query log records every statement that runs longer than a threshold. It is off by default on both servers, and the default threshold is ten seconds, which is far too generous for a web application. Switch it on without a restart:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';

The last statement tells you where the file is written. A new long_query_time applies to connections opened after the change, so restart PHP-FPM and any queue workers that keep their connections open. To make the setting survive a database restart, add it to the server configuration file:

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

On a managed database service the same variables are set in the provider's parameter settings. Leave log_queries_not_using_indexes off to begin with: it fills the log with harmless scans of ten-row lookup tables.

Collect a full day of normal traffic, then summarize. MySQL ships mysqldumpslow and MariaDB ships the same tool as mariadb-dumpslow. Both group statements that differ only in their values:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

The -s t option sorts by total time, and that is the order to work in. A 1.2-second query that runs 5,000 times a day costs more than a 20-second report someone runs on Monday mornings.

Read the plan with EXPLAIN before changing anything

Put EXPLAIN in front of a statement and the server describes how it intends to run it, without running it:

EXPLAIN SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Four columns of the output carry most of the information:

• type is how the table is read. ALL means every row is scanned. range, ref, eq_ref and const mean an index takes the server to the rows it wants.
• key is the index that was chosen. NULL means none was.
• rows is the estimated number of rows examined. Compare it with the number returned. Examining 800,000 rows to return 20 is the signature of a missing index.
• Extra holds the warnings. "Using filesort" means the rows are sorted after they are fetched. "Using temporary" means a temporary table was built. "Using index" is good news: the query was answered from the index alone.

Plain EXPLAIN shows estimates. To see what happened when the query ran, MySQL has EXPLAIN ANALYZE SELECT ... and MariaDB has ANALYZE SELECT ..., which adds the real row counts beside the estimated ones. Both execute the statement, so keep them for SELECT and away from updates and deletes on live data. The MySQL reference for EXPLAIN output and the MariaDB page on the ANALYZE statement list every value.

Build composite indexes in the order the query needs

The query above filters on two columns and sorts by a third. Three single-column indexes do not solve that, because the server generally picks one of them and does the rest by hand. One composite index does:

ALTER TABLE orders
  ADD INDEX idx_customer_status_created (customer_id, status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Column order is the whole skill. Put the columns compared with = first, then the column you sort by or filter with a range such as > or BETWEEN. With this index the server goes straight to that customer's paid orders, finds them already in date order, reads twenty entries and stops. "Using filesort" disappears from the plan.

Two consequences follow from how these indexes are stored. An index is usable only from its left edge: this one serves a filter on customer_id alone, or on customer_id and status, but not on status alone. And an existing single-column index on customer_id is now redundant. Run SHOW INDEX FROM orders; and drop duplicates, since every index has to be updated on every insert and update.

The ALGORITHM=INPLACE, LOCK=NONE clause asks for the index to be built while reads and writes continue, and makes the statement fail immediately if the server cannot do that, instead of locking the table without warning. InnoDB supports it for adding an ordinary index. The build still costs disk activity and can delay replicas on a large table, so time it on a copy and run it at a quiet hour.

Spot the queries that cannot use an index

Sometimes the right index exists and the plan still shows ALL. The cause is usually in how the condition is written.

A function wrapped around the column. The server cannot look up the result of a calculation in an index of raw values. Rewrite the condition so the column stands alone:

-- cannot use an index on created_at
WHERE DATE(created_at) = '2025-03-01'

-- can
WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02'

A leading wildcard. LIKE 'smith%' can use an index and LIKE '%smith' cannot. Searching inside text needs a FULLTEXT index or a separate search engine.

A type mismatch. If phone is a VARCHAR and the application sends WHERE phone = 5551234 as a number, the server converts every row to compare it. Bind the value as a string.

Selecting every column. SELECT * forces a visit to the table row even when an index holds the three columns the page displays.

N+1 queries never appear in the slow log

The other kind of slow page has no slow query at all. It runs one query for a list and then one more per row, and each of those takes a millisecond, so none crosses the threshold. You see it only as a count: a debug bar or query log showing 51 queries for a page that lists 50 orders.

// 1 query for the orders, then 1 per order for its customer
$orders = Order::latest()->take(50)->get();
foreach ($orders as $order) {
    echo $order->customer->name;
}

// 2 queries in total
$orders = Order::with('customer')->latest()->take(50)->get();

In Eloquent the fix is eager loading with with(), and withCount() when the page only needs a number such as "12 comments". Laravel can also refuse lazy loading outside production, which turns every new N+1 into an exception during development:

// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

public function boot(): void
{
    Model::preventLazyLoading(! $this->app->isProduction());
}

Without an ORM the same bug is a query inside a foreach. Collect the ids first, fetch the related rows with a single WHERE id IN (...), and index the result by id in PHP.

Paginate without counting to a million

LIMIT 20 OFFSET 200000 does not skip to row 200,000. The server reads 200,020 rows and throws away all but the last twenty, so each page is slower than the one before it. Keyset pagination remembers where the previous page ended and asks for the rows after that point:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
  AND (created_at < '2025-03-01 10:15:00'
       OR (created_at = '2025-03-01 10:15:00' AND id < 99120))
ORDER BY created_at DESC, id DESC
LIMIT 20;

The id breaks ties between rows with the same timestamp, and an index on (customer_id, created_at, id) makes every page cost the same as the first. The price is that you get "next" and "previous" but no jump to page 57. In Laravel, cursorPaginate() builds this query for you. If you stay with offsets, simplePaginate() at least drops the COUNT(*) that paginate() runs to draw page numbers, which on a large filtered table is often slower than the page itself.

How far to take database query optimization yourself

The slow log, EXPLAIN and one well-ordered index solve most cases, and the work is safe when you try it on a copy of production data first. If the log is clean and the page is still slow, the time is going somewhere else, and profiling the whole request will show where.

Bring in help when the fix stops being a query and becomes a design change: reports that need summary tables or a read replica, tables that have to be split or archived, lock waits and deadlocks between writers, or a schema change on a table too large to alter casually (our article on database migration with minimal downtime covers that last one). This is routine work in our performance optimization projects, and it is also part of ongoing Laravel maintenance and PHP application support, where the slow log is read every month and not only when someone complains.

Frequently asked questions

Does adding an index slow down writes?

Yes, a little. Each index is updated on every insert, and on every update that touches its columns, and it takes disk space and memory. One index that removes a full table scan from a busy page is worth that cost many times over. Ten overlapping indexes added "just in case" are not.

Why is a query fast on staging and slow in production?

Almost always because of data volume. A full scan of a 5,000-row staging table is instant, and the same plan on five million rows is not. Test with a production-sized copy, and compare the EXPLAIN output on both systems, since the optimizer chooses plans from table statistics that differ between them.

Should I run ANALYZE TABLE or OPTIMIZE TABLE regularly?

ANALYZE TABLE refreshes the statistics the optimizer uses and is cheap, so it is worth running after a large import or when the row estimates in EXPLAIN are far from reality. OPTIMIZE TABLE rebuilds the table to reclaim space. It is heavy on a large table and rarely makes queries faster, so do not schedule it out of habit.

Is it better to cache query results than to fix the query?

Fix first, cache second. A cache hides a slow query until the entry expires or is cleared, and then one visitor waits the full time while the result is rebuilt. Once the query is reasonable, caching results that change rarely is a sound way to cut load further.