Database Optimisation for PHP and MySQL Websites: Indexes, Slow Queries and Clean-Up

Many websites start fast and gradually slow down. Pages that loaded instantly with 100 bookings or products now take seconds with 50,000. Often the cause isn't the server or the design; it's the database. Here's how to keep PHP and MySQL (or MariaDB) websites fast as they grow. This guide is aimed at developers and technically minded owners.

Always back up the database before making structural changes.

Signs the database is the bottleneck

• Slow server response (high TTFB) even with page caching off
• Admin pages with lists, searches or reports are especially slow
• Hosting warnings about CPU or "entry process" limits
• Slowness grows with the amount of data

1. Add the right indexes

An index helps MySQL find rows without scanning the whole table, like a book's index. Add indexes on columns used in: - WHERE conditions (e.g. status, slug, city, created_at) - JOIN conditions (foreign keys) - ORDER BY columns used with filters

ALTER TABLE bookings ADD INDEX idx_status_created (status, created_at);

Composite indexes should match the order columns are used in queries. Don't index everything; each index slows down writes slightly and uses space.

2. Find slow queries

• Enable the slow query log (on a VPS) or check your host's tools
• Use EXPLAIN before a query to see whether it uses indexes:

EXPLAIN SELECT id, title FROM posts WHERE status='published' ORDER BY created_at DESC LIMIT 10;

If type shows ALL and rows is large, the query scans the full table, so an index is likely needed.

3. Select only what you need

Avoid SELECT * on large tables, especially with long text columns. Fetch only the columns the page displays. For listing pages, don't load full article content just to show titles.

4. Always paginate

Never load thousands of rows to show 20. Use LIMIT and OFFSET, or for very large tables, "keyset" pagination (WHERE id < last_id ORDER BY id DESC LIMIT 20).

5. Avoid queries in loops

A common pattern is running one query per item in a loop ("N+1 queries"). Instead, fetch related data in one query with a JOIN or WHERE id IN (...).

6. Use prepared statements

Prepared statements protect against SQL injection and let MySQL reuse query plans. See PHP security basics.

7. Watch collations and data types

• Use consistent character sets and collations (e.g. utf8mb4_unicode_ci) across related tables. Mismatches can prevent index use in joins and cause "illegal mix of collations" errors
• Use appropriate types: INT for IDs, DATE/DATETIME for dates, not text

8. Clean old data

• Archive or delete old logs, sessions, spam form entries and temporary records
• For WordPress, clean revisions, transients and orphaned metadata
• Run OPTIMIZE TABLE occasionally on tables with many deletions (check your engine and host guidance)

9. Cache heavy queries

For data that doesn't change every second (city lists, menus, counts, sitemaps), cache results in a file, APCu, Redis or a small cache table for a few minutes or hours. This removes repeated work on every page view.

10. Keep MySQL/MariaDB and PHP updated

Newer versions bring performance improvements and security fixes.

A quick routine

• Monthly: check slow pages in admin and front-end
• Quarterly: review the slow query log and table sizes
• Before launching a new feature: check its queries with EXPLAIN

Database tuning is part of our maintenance for PHP/MySQL sites we build and look after. Ask us to review your site's performance.