How to Optimise Database Queries

Who This Guide Is For

This guide is for developers whose application is slower than it should be and who suspect the database is the bottleneck — or developers who want to prevent that situation before it arrives. You have a working application with a relational database (MySQL or PostgreSQL), and you want to identify which queries are causing performance problems and fix them systematically rather than guessing.

Before You Start

You should have access to your database’s query log or slow query log, a profiling tool (Laravel Debugbar, Telescope, or the database’s built-in tools), and a dataset large enough to reveal performance issues. An application that runs perfectly with 100 rows will often struggle with 10,000 rows if the queries are not optimised. If your development database has minimal data, either seed it with realistic volumes or profile against a staging database with production-scale data.

This guide focuses on read query optimisation. Write-heavy workloads (bulk inserts, heavy updates) have different optimisation strategies and are not covered here.

Step 1: Identify the Actual Bottlenecks

The biggest optimisation mistake is guessing which queries are slow. Developers often optimise queries that are already fast while ignoring the ones causing real problems. Use profiling tools to find the actual bottlenecks.

Enable query logging in your development environment. In Laravel, Debugbar shows every query executed during a request, including execution time and the number of queries. Telescope provides similar visibility with a web interface. Both tools make it immediately obvious when a page is executing 200 queries instead of 5.

The slow query log is your database’s built-in mechanism for identifying problematic queries. In MySQL, enable it and set the threshold to a value appropriate for your application — one second is a reasonable starting point. In PostgreSQL, set log_min_duration_statement to capture queries above your threshold. Review the slow query log regularly, not just when users complain.

EXPLAIN is your primary diagnostic tool. Running EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) before a query shows the database’s execution plan: which indexes it uses, how many rows it examines, and whether it performs full table scans. Learn to read EXPLAIN output — the difference between a query that uses an index to find 10 rows and one that scans 10 million rows is not visible from the query text alone.

Identify the most impactful queries first. A query that takes 500 milliseconds but runs once per hour is less impactful than a query that takes 50 milliseconds but runs 1,000 times per minute. Prioritise by total time (frequency multiplied by duration), not by individual query duration.

Step 2: Fix N+1 Query Problems

The N+1 problem is the most common performance issue in applications that use an ORM (Object-Relational Mapper). It occurs when the application executes one query to fetch a list of records and then executes an additional query for each record to fetch related data. For a list of 100 items, this means 101 queries instead of 2.

How N+1 queries happen in Eloquent. When you load a collection of models and then access a relationship in a loop, Eloquent executes a separate query for each model. If you load 50 orders and then access the customer for each order inside a loop, Eloquent runs one query for the orders and 50 queries for the customers.

Eager loading is the fix. Use the with method to load related models in a single query rather than one query per model. Loading orders with their customers eager-loaded results in two queries (one for orders, one for all related customers) regardless of how many orders there are. The query count drops from N+1 to 2.

Nested eager loading handles deeper relationships. If you need orders with their customers and each customer’s address, eager load the chain in a single with call using dot notation. This produces three queries total instead of potentially hundreds.

Detect N+1 queries automatically. Laravel has a preventLazyLoading method that throws an exception in development when a lazy-loaded relationship is accessed. Enabling this catches N+1 problems during development before they reach production. It should be enabled in local and testing environments and disabled in production (where it would throw exceptions visible to users).

Not every relationship should be eager loaded. If you load an order but only sometimes access the customer (based on a conditional), eager loading the customer on every query wastes resources when the relationship is not used. Load eagerly when you know the relationship will be accessed for most or all records in the collection.

Step 3: Add the Right Indexes

Indexes are the most powerful tool for query optimisation, and missing indexes are the most common cause of slow queries. An index allows the database to find rows without scanning the entire table — similar to looking up a word in a book’s index rather than reading every page.

Index columns used in WHERE clauses. If your application frequently queries orders by customer_id, an index on customer_id allows the database to jump directly to the matching rows. Without the index, the database reads every row in the table to find matches. On a table with millions of rows, this is the difference between a 2-millisecond query and a 2-second query.

Index columns used in JOIN conditions. When joining two tables, both sides of the join condition should be indexed. Foreign key columns should always have indexes — some databases create them automatically when you define a foreign key constraint, but not all do. Verify that your foreign keys are indexed.

Composite indexes cover queries that filter on multiple columns. If your application frequently queries orders by customer_id and status, a composite index on (customer_id, status) is more effective than two separate indexes. The column order in a composite index matters — put the most selective column first (the one that narrows the result set the most).

Do not over-index. Every index speeds up reads but slows down writes, because the index must be updated whenever the indexed data changes. A table with 15 indexes on a write-heavy workload will have slow inserts and updates. Index the columns your application actually queries, not every column in the table.

Use EXPLAIN to verify indexes are used. After adding an index, run EXPLAIN on the query to confirm the database is actually using it. Sometimes the query optimiser decides a full table scan is faster (for very small tables or when the query would return most of the table’s rows). If the index is not being used, the column statistics may be outdated — run an ANALYZE command to update them.

Step 4: Optimise Query Patterns

Beyond indexes and eager loading, the structure of your queries affects performance. Several common patterns cause unnecessary work.

Select only the columns you need. Selecting all columns when you only need three wastes memory and bandwidth, especially for tables with large text columns, JSON columns, or many columns. In Eloquent, use the select method to specify which columns to retrieve. This is particularly impactful for list views where you display summary data — you do not need the full content of every record.

Avoid queries inside loops. Any query executed inside a loop is a potential N+1 problem, even if it is not a relationship query. If you need to look up data for each item in a collection, fetch all the data upfront with a single query and look it up from the in-memory collection. This includes existence checks, counts, and auxiliary data lookups.

Use chunking for large datasets. If you need to process thousands of records (for a report, an export, or a batch update), do not load them all into memory at once. Use chunk processing (in Eloquent, the chunk or lazy method) to process records in batches. This keeps memory usage constant regardless of the total number of records.

Aggregate in the database, not in PHP. If you need the count, sum, average, or maximum of a dataset, use the database’s aggregate functions rather than loading all the records into PHP and computing the result in application code. The database is optimised for these operations and can use indexes to compute them without scanning every row.

Subqueries instead of multiple queries. If you need to filter based on a calculation (such as orders where the total exceeds the customer’s average order value), a subquery computes the value in the database rather than requiring a separate query followed by application-level filtering. Eloquent supports subqueries through the addSelect and whereHas methods.

Step 5: Monitor and Maintain Performance

Database optimisation is not a one-time task. Query performance changes as data grows, usage patterns shift, and new features add new queries.

Monitor query performance in production. Use a tool that tracks query execution times in production (New Relic, Datadog, Laravel Telescope, or the database’s performance schema). Development and staging environments often have different data volumes and different performance characteristics than production. A query that is fast in development may be slow in production.

Review query performance after data growth milestones. A table that performs well at 100,000 rows may struggle at 1,000,000 rows. When your application crosses data volume thresholds, review the slow query log and re-examine your indexing strategy.

Maintain index health. Over time, indexes can become fragmented, especially on tables with heavy insert and delete activity. Most databases handle this automatically, but for high-traffic tables, check index fragmentation periodically and rebuild indexes if fragmentation is high.

Archive old data. If your application accumulates historical data that is rarely accessed (old logs, completed orders from years ago, expired sessions), archive it to a separate table or database. This keeps the active tables small, which improves index effectiveness and query performance. Ensure the archive is still accessible for reporting and compliance, but do not let it slow down the queries that serve active users.

Cache expensive query results. For queries that are slow by nature (complex aggregations, multi-table joins, full-text searches), cache the results with an appropriate TTL. This trades freshness for speed — acceptable for dashboards and reports, unacceptable for real-time transactional data. Use cache invalidation tied to the underlying data changes so that cached results are refreshed when the data they represent changes.

Common Mistakes

• Optimising without profiling. Guessing which queries are slow wastes time on queries that are already fast. Profile first, then optimise the actual bottlenecks.
• Ignoring N+1 queries. The application works in development with 10 records. In production with 10,000 records, it executes 10,001 queries per page. Enable lazy loading prevention in development.
• Over-indexing. Indexes on every column slow down writes and consume storage. Index only the columns used in WHERE, JOIN, and ORDER BY clauses in your actual queries.
• *Using SELECT everywhere.** Loading every column for every query wastes memory and network bandwidth. Select only the columns the feature needs.
• Never looking at EXPLAIN output. Without EXPLAIN, you cannot verify that your indexes are being used or understand why a query is slow. Make EXPLAIN a regular part of query development.

What Good Looks Like

A well-optimised database layer has: profiling enabled in development that flags N+1 queries and slow queries automatically, indexes on columns used in WHERE, JOIN, and ORDER BY clauses, eager loading for relationships accessed in collections, queries that select only the columns needed, aggregate calculations performed in the database rather than in application code, production monitoring that tracks query performance over time, and a periodic review process that catches performance degradation as data grows.

Next Steps

For the dashboard queries that often need the most optimisation, How to Build a Reporting Dashboard With Real-Time Data covers data aggregation patterns. For the CI/CD pipeline that can include query performance checks, How to Set Up CI/CD for a Laravel Project covers automated testing. For choosing the right database in the first place, see How to Choose the Right Database for Your Project.