Hey there paid subscriber!

Welcome to the enhanced version of ORMs are great... until they aren't. It comes with jokes…

Why do front end developers eat lunch alone?
They don't know how to join tables.

… And more tips.

First, read the first article [here] and then continue below…

Filter Early with `WHERE`

Reduce row counts as early as possible to limit the data processed in joins, aggregations, and sorting. This minimises memory usage and speeds up query execution.

-- Inefficient (filters after join)
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'UK';

-- Better (filter first with WHERE on both sides if needed)
SELECT * FROM customers c
WHERE c.country = 'UK'
JOIN orders o ON o.customer_id = c.id;

Use `EXPLAIN` to Analyse Queries

EXPLAIN shows how the database executes your query — including index usage, join strategies, and estimated row counts.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

The output looks something like this:

What to look for:

type should ideally be ref, const, or eq_ref. If it says ALL, it’s scanning the full table.
key shows the index being used.
rows tells you how many rows it expects to scan.
Extra often reveals if it’s using temporary tables or filesorts — red flags for performance.

Beware of N+1 Queries

When using ORMs, fetching related models inside a loop results in multiple queries.

// Bad
foreach (User::all() as $user) {
    echo $user->posts->count(); // Triggers a query every time
}

// Good: With eager loading
$users = User::with('posts')->get();
foreach ($users as $user) {
    echo $user->posts->count();
}

Limit Result Sets with `LIMIT`

Don’t fetch thousands of rows if you only need a few — paginate results where appropriate.

SELECT * FROM products ORDER BY created_at DESC LIMIT 20;

This avoids overloading the client and the database with unnecessary rows.

Avoid Functions on Indexed Columns in `WHERE`

Using functions like LOWER() or DATE() in WHERE prevents the index from being used.

// Bad
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

// Good: Store lowercase emails ahead of time
SELECT * FROM users WHERE email = '[email protected]';

Use Joins Instead of Subqueries

Joins are usually more efficient and easier to optimise than subqueries.

// Bad: Using a sub query
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

// Good: Using a JOIN
SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id;

But why?

Subqueries can be slow — especially if they run for every row in the outer query (called correlated subqueries).

Joins are easier for the database to plan and optimise — they give it more freedom to pick the best way to fetch the data.
Joins can use indexes better — meaning your queries will be faster and use less memory.

Sometimes subqueries are useful — like when you're summarising data first, then joining. But most of the time, if you're just filtering or looking up related rows, a join is better.

Avoid `DISTINCT` Unless Necessary

It forces extra work — check if you can fix duplicates in other ways. DISTINCT forces the DB to sort or hash rows to eliminate duplicates, which is costly.

// Bad
SELECT DISTINCT email FROM users WHERE status = 'active';

// Good: If email is already UNIQUE, DISTINCT isn’t needed
SELECT email FROM users WHERE status = 'active';

Be Careful with `OR` Conditions

They can prevent indexes from being used efficiently; consider UNION of multiple queries if needed. OR can block index use.

// Bad
SELECT name FROM posts WHERE author_id = 5 OR category_id = 3;

// Good:
SELECT name FROM posts WHERE author_id = 5
UNION
SELECT name FROM posts WHERE category_id = 3;

When you use OR in your WHERE clause, the database may not be able to use indexes efficiently, especially if each condition involves different columns. It ends up scanning more rows than needed.

Why UNION can help:
Breaking the query into two separate parts (one for each condition) and combining them with UNION gives the database two smaller, focused queries. Each can use an index, and you still get the combined result.

One messy OR condition = hard for the database to optimise.
Two clean queries with UNION = easier, faster.

Use Composite Indexes for Multiple Conditions

When filtering on multiple columns, a composite index can speed up lookups.

What is a composite index?
It's an index that covers more than one column, in a defined order.

CREATE INDEX idx_users_status_created ON users(status, created_at);

-- This query can now use that index
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01'

Why it helps: The database can jump straight to rows that match both conditions using one index, instead of scanning through one and filtering the other manually.

Note: The order of columns in the index matters. Indexes on (status, created_at) help queries that filter by status or both, but not ones that filter by created_at alone.

Archive or Delete Old Data

If your table keeps growing, queries slow down. Move old data to archive tables or delete it.

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 6 MONTH;

Avoid Implicit Data Type Conversions

Mismatched types stop indexes from working.

// Error: phone_number is VARCHAR, but filter is INT
SELECT * FROM users WHERE phone_number = 123456789;

// Fix
SELECT * FROM users WHERE phone_number = '123456789';

Cache Frequent Queries

If you’re hitting the same query a lot, use database or app-layer caching.

SELECT COUNT(*) FROM posts WHERE status = 'published';

Test with Realistic Data

A query might fly with 100 rows but crawl with 10 million. Always test performance on data that mirrors production size and distribution.

Tip: Use a staging DB with real data volumes, and EXPLAIN ANALYZE to see execution time and plan.

And thats it!

Thank you for being a supporter of my newsletter. You’re awesome.

Filter Early with WHERE

Use EXPLAIN to Analyse Queries