Database Query Optimization Basics

Most PHP pages spend their time waiting for the database. A few habits make a big difference.

1. Select only what you need

1
2
3
4
5

-- Loads every column, including large TEXT fields
SELECT * FROM posts;

-- Loads only what the list shows
SELECT id, title, created_at FROM posts;

Less data to read, send and hold in PHP memory.

2. Limit and paginate

Never load a whole table. Use LIMIT and OFFSET (see Pagination Basics). For huge tables, keyset pagination (WHERE id < :last_id ORDER BY id DESC LIMIT 20) avoids the cost of large offsets.

3. Index the columns you search

An index is like a book’s index: the database jumps to the right rows instead of reading every row (a “full table scan”).

Add indexes for columns used in:

• WHERE conditions
• JOIN ... ON conditions
• ORDER BY (sometimes)

1
2

CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_posts_user_date ON posts (user_id, created_at);

The second one is a composite index. It helps queries that filter by user_id and then sort by created_at. Column order matters: the index helps when the query uses the leftmost columns first.

Primary keys and UNIQUE columns already have an index.

Indexes are not free

Each index uses disk space and makes INSERT, UPDATE and DELETE a little slower. Add indexes where queries need them, not on every column.

4. Look at the plan with EXPLAIN

Put EXPLAIN in front of a query, and MySQL describes how it will run it.

1

EXPLAIN SELECT id, name FROM products WHERE category = 'paper';

In the result, look at:

If a slow query shows type: ALL and key: NULL, an index is probably missing.

5. Write searchable conditions

A function on a column often prevents the index from being used.

1
2
3
4
5

-- Cannot use an index on created_at
SELECT id FROM posts WHERE YEAR(created_at) = 2026;

-- Can use the index
SELECT id FROM posts WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Also, LIKE '%term' (a leading wildcard) cannot use a normal index. LIKE 'term%' can.

6. Avoid N+1 queries

Fetch related data with a JOIN or a single WHERE id IN (...), not one query per row. See Avoiding Repeated Work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<?php
$pdo = require __DIR__ . "/db.php";

$rows = $pdo->query(
    "SELECT p.name AS product, COUNT(*) AS same_category
       FROM products p
       JOIN products q ON q.category = p.category
      GROUP BY p.id, p.name
      ORDER BY p.id"
)->fetchAll();

foreach (array_slice($rows, 0, 3) as $r) {
    echo $r["product"] . ": " . $r["same_category"] . "\n";
}

1
2
3

Notebook: 2
Pen: 2
Bag: 1

One query gave every product and the size of its category.

7. Use the right data types

A VARCHAR(255) for a yes/no flag, or comparing a number column to a string, wastes space and can stop indexes from working. Use INT, TINYINT, DATE and DECIMAL for what they are.

8. Count carefully

SELECT COUNT(*) FROM big_table can be slow on very large tables. Cache totals, or use an approximate count when an exact one is not needed.

9. Keep transactions short

A transaction that stays open while PHP does slow work (an API call) blocks other queries. Do the slow work first, then open the transaction, write quickly and commit.

10. Log slow queries

MySQL has a slow query log (slow_query_log = 1, long_query_time = 1). It records queries that take longer than a threshold. Review it regularly, and fix the worst ones first.

Measure before and after

Run EXPLAIN before adding an index, and again after. If the plan did not change, the index did not help.