An application can have well-structured PHP code, adequate infrastructure, and still be slow because of a few SQL queries.

The problem is that when a page takes five seconds to load, it is common to start looking in the wrong place: PHP, the server, the network, Docker, caching, or even the database as a whole.

In practice, the problem may be a single query.

There is an important difference between saying “the database is slow” and discovering that a specific query is consuming too many resources.

In this article, I will show a practical approach to identifying these queries, understanding why they are slow, and validating whether a change actually improved performance.

The First Mistake: Optimizing Without Measuring

Before changing indexes, rewriting SQL, or increasing server resources, we need to answer a few questions:

• Which query is slow?
• How long does it take?
• How often is it executed?
• How many rows are being examined?
• How many rows are actually returned?
• Is the database using an index?
• Is there a JOIN, SORT, GROUP BY, or filter consuming significant resources?
• Does the problem happen consistently or only under specific conditions?

Without this information, optimization becomes trial and error.

A query that takes 500 ms and runs once per minute may be less problematic than a query that takes 50 ms but runs hundreds of times per second.

Therefore, execution time and frequency need to be analyzed together.

1. First, Find Out Where the Slowdown Is

Before blaming SQL, find out which layer is consuming the time.

An HTTP request can go through several stages:

Browser
   ↓
Web Server
   ↓
PHP
   ↓
Application
   ↓
Database connection
   ↓
SQL
   ↓
Database execution
   ↓
PHP processing
   ↓
Response

A page that takes three seconds does not necessarily mean that the SQL query took three seconds.

For example:

HTTP request:       3.20s
PHP execution:      3.00s
SQL queries:        0.20s

In this case, looking for a database index probably will not solve the problem.

Now consider:

HTTP request:       3.20s
PHP execution:      3.15s
SQL queries:        2.95s

Here, we have strong evidence that the database deserves investigation.

One of the first improvements worth implementing is measuring the execution time of critical operations.

2. Measuring Queries in PHP

During an investigation, we can measure a query directly:

$start = microtime(true);

$stmt = $pdo->prepare(
    'SELECT id, name
     FROM customers
     WHERE email = :email'
);

$stmt->execute([
    'email' => $email,
]);

$result = $stmt->fetch();

$elapsed = microtime(true) - $start;

error_log(sprintf(
    'SQL executed in %.4f seconds',
    $elapsed
));

This is useful for troubleshooting, but it should not be considered a replacement for application observability.

In a real project, it is better to record information such as:

route
controller
query
duration
database
rows
request_id

and correlate this information with logs or observability tools.

It is also important to avoid logging sensitive data or query parameters indiscriminately.

3. Look for Slow Queries in the Database

After confirming that the database is involved, the next question is:

Which query is causing the problem?

In MySQL, the Slow Query Log is one of the most useful tools for this type of investigation. It records queries that exceed the configured long_query_time threshold and can provide information such as execution time, lock time, rows returned, and rows examined.

For example:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

This can be useful during a controlled investigation, but the configuration should be planned according to the environment and workload.

MySQL can also be configured to log queries that do not use indexes. However, this can generate a significant amount of log data on busy systems.

Once we have identified suspicious queries, we can start analyzing their execution plans.

4. In PostgreSQL, Use pg_stat_statements

In PostgreSQL, the pg_stat_statements extension is particularly useful.

It keeps planning and execution statistics for SQL statements executed by the server, allowing us to identify important queries based on accumulated behavior rather than a single execution.

For example:

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This helps identify a type of problem that is easy to overlook:

Query A
Average time: 1.2s
Executions: 10

Query B
Average time: 30ms
Executions: 100,000

The first query is individually much slower.

But the second query may be consuming much more total database time.

That is why we should not look only for the slowest query. Frequency matters too.

5. The Main Tool: EXPLAIN

Found a suspicious query?

Now we need to understand how the database intends to execute it.

In MySQL:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 123;

EXPLAIN provides the execution plan and helps us understand how the database intends to access tables and use indexes.

In PostgreSQL:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 123;

The concept is similar. PostgreSQL shows the plan produced by the planner, including operations such as Seq Scan, Index Scan, joins, aggregations, and sorting.

The goal is not to memorize every EXPLAIN field.

The goal is to learn to ask:

Is the database doing what I expected?

6. EXPLAIN Shows the Plan. EXPLAIN ANALYZE Shows What Actually Happened

This distinction is extremely important.

In MySQL 8.4, EXPLAIN ANALYZE executes the query and adds information such as estimated cost, estimated rows, actual timing, and the number of loops for each iterator.

For example:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 123;

In PostgreSQL:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 123;

PostgreSQL also executes the query and reports actual execution times and row counts for each plan node.

This allows us to identify situations such as:

Estimated: 10 rows
Actual:    150,000 rows

A large difference between estimated and actual rows can indicate that the planner is making decisions based on inadequate statistics or that the data distribution is different from what was expected.

In PostgreSQL, for example, statistics collected by ANALYZE are used by the planner when building execution plans.

7. Be Careful: EXPLAIN ANALYZE Executes the Query

This detail is particularly important for INSERT, UPDATE, and DELETE.

Consider:

EXPLAIN ANALYZE
UPDATE accounts
SET balance = balance + 100
WHERE id = 10;

The query will actually be executed.

In PostgreSQL, the documentation recommends using a transaction with ROLLBACK when you need to analyze a data-modifying operation without keeping its effects:

BEGIN;

EXPLAIN ANALYZE
UPDATE accounts
SET balance = balance + 100
WHERE id = 10;

ROLLBACK;

In production, this type of test requires even more care.

8. One of the Most Common Problems: Missing Indexes

Consider:

SELECT id, name, email
FROM customers
WHERE email = '[email protected]';

If email is not indexed and the table contains millions of records, the database may need to examine a huge number of rows.

A possible solution:

CREATE INDEX idx_customers_email
ON customers (email);

Then:

EXPLAIN
SELECT id, name, email
FROM customers
WHERE email = '[email protected]';

Indexes are one of the first things to investigate when a SELECT ... WHERE query is slow.

But there is an important caveat:

This does not mean that every column used in a WHERE clause should have an index.

Indexes consume storage and need to be maintained during write operations.

The right question is:

Does this index improve an important query enough to justify its cost?

9. Be Careful With Functions Applied to Filtered Columns

Consider:

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

Although this looks simple, we are applying a function to the column.

An approach that is generally friendlier to index usage would be:

SELECT *
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

The general idea is to avoid transforming the column before the database has a chance to use an available index efficiently.

The same principle can appear in several forms:

WHERE DATE(created_at) = ...

WHERE LOWER(email) = ...

WHERE CAST(code AS CHAR) = ...

This does not mean that functions are always bad.

It means we should verify how they affect the execution plan.

10. SELECT * Can Also Be a Problem

Consider:

SELECT *
FROM customers
WHERE id = 10;

If we only need:

id
name
email

we can use:

SELECT id, name, email
FROM customers
WHERE id = 10;

This reduces the amount of data transferred and may enable more efficient access strategies in some cases.

In a large table with many columns, returning unnecessary data also increases the cost between the database, PHP, and the application.

This does not mean that SELECT * automatically makes a query slow.

It means we should avoid retrieving data that the application does not actually need.

11. JOIN Is Not the Villain

It is common to see something like:

SELECT *
FROM orders o
JOIN customers c
    ON c.id = o.customer_id;

and immediately conclude that the JOIN is the problem.

But JOIN is a fundamental relational database operation.

The questions should be:

• Which join algorithm was selected?
• How many rows exist on each side?
• Are the columns used in the relationship indexed when appropriate?
• Are there suitable filters?
• How many rows are produced before filtering?

A query with several JOINs can be extremely fast.

A seemingly simple query can be extremely slow.

The execution plan tells us what is actually happening.

12. Watch Out for the N+1 Problem

This is one of the most common problems in PHP applications using ORMs.

Imagine that the application retrieves 100 orders:

SELECT *
FROM orders
LIMIT 100;

Then, for every order:

SELECT *
FROM customers
WHERE id = ?;

We can end up with:

1 query for orders
+
100 queries for customers
=
101 queries

Each individual query may look fast.

The problem is the total number of queries.

Depending on the use case, we could instead use a JOIN, eager loading, or another appropriate strategy:

SELECT
    o.id,
    o.amount,
    c.id AS customer_id,
    c.name AS customer_name
FROM orders o
JOIN customers c
    ON c.id = o.customer_id
LIMIT 100;

This is particularly important in applications using Doctrine, Eloquent, or other ORMs.

The abstraction provided by an ORM makes development easier, but it does not eliminate the cost of the queries it generates.

13. OFFSET Pagination Can Become More Expensive as the Table Grows

A common pagination query is:

SELECT *
FROM orders
ORDER BY id DESC
LIMIT 50 OFFSET 500000;

It looks perfectly reasonable.

But as the OFFSET grows, the database may need to walk through an increasingly large number of records before returning the next 50.

For large tables, cursor-based or keyset pagination may be more appropriate:

SELECT *
FROM orders
WHERE id < 500000
ORDER BY id DESC
LIMIT 50;

The right solution depends on the navigation pattern and available indexes.

The important point is:

Pagination itself needs to be analyzed as part of query performance.

14. Too Many Rows Examined for Too Few Rows Returned

This is one of the signals I like to pay attention to.

Imagine:

Rows examined: 1,000,000
Rows returned: 20

This deserves investigation.

Of course, not every query that examines many rows is wrong. There are cases where this is expected.

But when the database needs to examine thousands or millions of records to return a few results, we should ask:

Is there an appropriate index?
Is the filter being applied early?
Is the index column order appropriate?
Is the JOIN producing too many rows?
Are the statistics up to date?

Tools such as MySQL’s EXPLAIN and slow query log can help with exactly this type of diagnosis.

15. Not Every Seq Scan Is a Problem

In PostgreSQL, finding:

Seq Scan

does not automatically mean that something is wrong.

If a table contains only a few hundred records, scanning the entire table may be cheaper than using an index.

The planner makes decisions based on estimated costs and available statistics.

Therefore:

Seq Scan = problem

is an overly simplistic conclusion.

The right question is:

Given the current size and data distribution of this table, is this execution plan appropriate?

16. Sorting Can Also Be Expensive

Consider:

SELECT id, name
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC;

Even with an efficient filter, sorting can introduce additional cost.

Depending on the database, data distribution, and query, an appropriate index may help:

CREATE INDEX idx_customers_status_created
ON customers (status, created_at);

But we should not automatically create this index simply because WHERE and ORDER BY appear in the query.

We should validate the execution plan before and after the change.

17. Outdated Statistics Can Also Affect the Execution Plan

The database needs information about the data to choose an appropriate execution plan.

In PostgreSQL, ANALYZE collects statistics used by the planner.

In MySQL, keeping optimizer statistics up to date is also part of query optimization.

This means that when a table undergoes significant changes, we should also consider the state of its statistics.

Sometimes the SQL did not change.

The indexes did not change.

But the distribution of the data changed significantly.

And the execution plan selected by the database may no longer be appropriate.

18. Locks Can Also Make a Query Look Slow

Imagine:

Query A → 20 ms
Query B → 25 ms
Query C → 10 ms

All of them are fast.

But one query spends five seconds waiting for a lock.

From the application’s perspective:

SQL took 5 seconds

But the problem is not necessarily query processing.

It may be transaction concurrency.

That is why a performance investigation should consider:

CPU
I/O
locks
connections
wait time
SQL execution
data volume

and not just the query text.

19. Make One Change at a Time

A common mistake during optimization is changing everything at once:

new index
+
rewritten SQL
+
cache
+
database configuration
+
PHP changes

Then:

It became faster!

But we do not know why.

A better approach is:

1. Measure
2. Identify
3. Change
4. Measure again
5. Compare

For example:

Before
Query: 1.82s
Rows examined: 850,000

Change
Index on (status, created_at)

After
Query: 0.07s
Rows examined: 320

Now we have evidence that the change had an impact.

That is the difference between adding an index and optimizing a query.

20. Do Not Compare Only a Single Execution

Imagine:

Run 1: 80ms
Run 2: 20ms
Run 3: 18ms
Run 4: 22ms

This can happen for several reasons, including caching, I/O, and temporary system load.

Performance should therefore be analyzed with repeatable measurements.

Whenever possible, compare:

average execution time
percentiles
number of executions
rows examined
rows returned
execution plan
index usage

And test with data that is representative of the real environment.

Results obtained from small tables should not automatically be extrapolated to much larger tables, because the database planner may choose different execution plans as data volume changes.

21. A Complete Example of a Query Investigation

Imagine that an orders page is taking approximately three seconds to load.

We find this query:

SELECT *
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC;

First:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC;

We discover that the query is examining a huge number of records.

Then we inspect the indexes:

SHOW INDEX FROM orders;

In PostgreSQL, we could inspect the table’s indexes or analyze the execution plan directly with EXPLAIN.

We create an index that matches the actual access pattern:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Then we run the query again:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC;

And, in an appropriate environment, we also validate it with EXPLAIN ANALYZE.

The goal is not simply to discover that “an index is now being used.”

We want to verify:

Did the number of rows examined decrease?
Did sorting become cheaper?
Did execution time decrease?
Does the plan remain appropriate as the data grows?

That is the difference between adding an index and optimizing a query.

22. A Slow Query Does Not Necessarily Mean Bad SQL

This is an important point.

A query may be slow because:

The table has grown significantly

or:

The amount of returned data is huge

or:

There is heavy concurrency

or:

There is lock contention

or:

The server is under I/O pressure

or:

Statistics are inaccurate or outdated

or:

The application executes the same query thousands of times

or even:

The database is fast, but PHP spends too much time processing the returned data.

That is why performance requires diagnosis.

23. My Checklist for Investigating a Slow SQL Query

When I find a problematic query, I usually follow this sequence:

[ ] Confirm that the database is actually the bottleneck

[ ] Measure query execution time

[ ] Find out how many times it is executed

[ ] Check the number of rows returned

[ ] Check the number of rows examined

[ ] Run EXPLAIN

[ ] Run EXPLAIN ANALYZE when appropriate

[ ] Check which indexes are being used

[ ] Check filters and JOINs

[ ] Look for functions applied to filtered columns

[ ] Check ORDER BY / GROUP BY

[ ] Look for SELECT *

[ ] Check pagination

[ ] Look for N+1 queries

[ ] Check locks and concurrency

[ ] Check database statistics

[ ] Make one change at a time

[ ] Measure again

[ ] Validate with representative data

Conclusion

Most SQL performance problems are not solved simply by “adding an index.”

A more reliable process is:

Measure
  ↓
Find the query
  ↓
Analyze the execution plan
  ↓
Understand the bottleneck
  ↓
Change
  ↓
Measure again

Tools such as Slow Query Log, EXPLAIN, EXPLAIN ANALYZE, and, in PostgreSQL, pg_stat_statements, make this process much more objective.

The main point is not to confuse symptoms with causes.

A slow page does not necessarily mean that PHP is slow.

High database CPU usage does not necessarily mean that every query is poorly written.

And a Seq Scan does not automatically mean that something is wrong.

The better approach is to turn suspicion into evidence.

Database performance should not be a guessing game. It should be a process of measurement, analysis, and validation.