A slow database is rarely solved by adding an index and hoping for the best. Indexes are essential, but they are only one part of the execution path. A query can still be slow because it reads too many rows, joins data in the wrong order, performs work the application does not need, or forces the database to build large temporary result sets.

Query optimization starts with a simple shift in mindset: do not ask, “Which index is missing?” Ask, “What work is this query asking the database to do?” Once that work is visible, the right fix is often smaller, safer, and more durable than an index added under pressure.

Measure the Query You Actually Run

Application code frequently hides the final SQL behind an ORM, query builder, repository, or API layer. That abstraction is useful, but it can also conceal expensive choices: selecting every column, loading relationships one request at a time, or applying filters after retrieving a broad result set.

Start with the exact query and its real parameters. Then inspect its execution plan with the database’s explain facility. The plan reveals whether the database scans a table, uses an index, sorts rows, creates a temporary structure, or repeatedly executes part of the query.

EXPLAIN
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND published_at >= '2025-01-01'
ORDER BY published_at DESC
LIMIT 20;

An execution plan is not a scorecard with one universally “good” shape. A table scan can be perfectly reasonable for a small table. The useful question is whether the plan’s work matches the data volume and request pattern. If a homepage needs 20 rows but the plan reads and sorts hundreds of thousands, there is a mismatch worth investigating.

Reduce Work Before Tuning Access Paths

The most reliable performance improvement is often doing less work. Returning fewer columns reduces network transfer, memory use, serialization cost, and cache pressure. Returning fewer rows has an even larger effect.

SELECT * is convenient during development, but it is a poor default for a high-traffic endpoint. If an API response needs an identifier, title, and timestamp, request those fields explicitly. This also makes dependencies clearer when schemas evolve.

Pagination deserves the same scrutiny. Offset pagination is easy to implement, but deep offsets can require the database to walk past many rows before it can return a page. For ordered, append-heavy data, keyset pagination is often a better fit.

SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND (published_at, id) < ('2025-01-15 10:00:00', 8421)
ORDER BY published_at DESC, id DESC
LIMIT 20;

The cursor must match the ordering columns, including a stable tie-breaker such as id. This is not merely a performance detail: a stable order helps prevent duplicates or skipped records as new rows arrive.

Make Filters and Indexes Work Together

Indexes accelerate specific access patterns, not arbitrary SQL. An index should reflect how the query filters, joins, and orders data. For the article query above, a composite index beginning with status and continuing with published_at may be appropriate when those conditions are common. Whether to include id depends on the database, the exact ordering, and the plan.

Column order matters. In general, an index is most helpful when its leading columns align with predicates and ordering that appear together in real queries. But rules of thumb are not substitutes for testing. Data distribution matters: indexing a column with very few distinct values may offer little benefit unless it is combined thoughtfully with other columns.

Also watch for expressions that make an otherwise useful index harder to use. This query applies a function to every candidate timestamp:

SELECT id
FROM events
WHERE DATE(created_at) = '2025-01-15';

When the intent is a calendar-day range, express it as a range on the stored value instead:

SELECT id
FROM events
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at < '2025-01-16 00:00:00';

The second form is usually easier for an index on created_at to support, while also making the boundary conditions explicit.

Beware the N+1 Query Pattern

Some database problems are not caused by one bad query. They are caused by a reasonable query repeated far too often. A classic example is fetching a list of orders and then querying the customer for each order. It may appear fast with a handful of records and become costly as page size or traffic grows.

In PHP applications using an ORM, eager loading can help when the related data is genuinely needed. In other cases, a join, a batched lookup using WHERE id IN (...), or a deliberately shaped read model may be clearer. The goal is not to eliminate all multiple queries; it is to make query count intentional and bounded.

• Log query count and duration for representative requests.
• Check whether loops trigger database access.
• Load only the relations and columns required by the response.
• Set practical page-size limits for endpoints that expand related data.

Joins Need Cardinality Discipline

Joins are powerful, but they can multiply rows in surprising ways. Joining orders to order items is correct when item-level rows are required. It is not correct to join them and then assume each resulting row represents one order. That mistake can inflate counts, duplicate API payloads, and create expensive deduplication through DISTINCT.

Before adding DISTINCT, identify why duplicates exist. Sometimes the query needs aggregation; sometimes it needs EXISTS rather than a join; sometimes the application should issue a separate, bounded query. Treat DISTINCT as a deliberate semantic operation, not a cosmetic repair.

Optimize the Whole Request Path

A database query may be fast in isolation while the endpoint remains slow because of connection setup, repeated queries, oversized JSON responses, cache misses, or downstream API calls. Performance work should follow the request from entry to response.

For backend teams, that means establishing a repeatable habit: capture slow queries, reproduce them with realistic parameters, inspect the plan, change one thing, and measure again. Test changes against production-like data volumes when possible. A query that performs beautifully with a local dataset may behave differently once distributions, concurrency, and cache state change.

Optimization is not about collecting clever SQL tricks. It is about making the database perform only the work the product requires, with access paths that match real usage. Indexes remain invaluable, but the lasting gains come from understanding the query’s intent, its plan, and its place in the broader system. When those align, speed becomes a property of the design rather than a lucky side effect.