MySQL Indexing: From 2-Second Searches to 30ms Results

🗄️ PHP · Updated June 2026 · 14 min read · by Jacob Campbell

I’m Jacob Campbell, and the single biggest backend speed win I see is also the most overlooked: indexing. A proper index can turn a 2-second product search into 30ms — a 98% improvement. Yet most WordPress sites use only default indexes, and many custom PHP apps are built with no indexing strategy at all. This guide shows you how to design indexes that make your database roar.

Key facts

• 98% — Best-case time cut
• 2s→30ms — Real example
• EXPLAIN — Find missing indexes
• ~6ms — Indexed lookup

What an index actually does

An index is a sorted lookup structure (usually a B-tree) that lets MySQL find rows without scanning the whole table — much like the index at the back of a book. Without one, a filtered query does a full table scan, reading every row; with one, it goes straight to the matches. MySQL’s indexing documentation explains the mechanics.

Why indexing matters for web performance

When a user hits a page that queries the database (product listings, search results, user profiles), MySQL must find the relevant rows. Without indexes it does a full table scan — reading every row until it finds matches. On a 100,000-row table, a full scan takes 200–500ms. With 1,000 concurrent users, that becomes seconds of delay and the server CPU saturates.

Indexes are like a book’s index: they tell MySQL exactly where to find rows, turning an O(n) operation into O(log n). The result is queries that return in milliseconds instead of seconds.

How MySQL indexes work (in simple terms)

MySQL uses B-tree indexes by default. They store column values in a sorted tree. When you run a query with a WHERE clause on an indexed column, MySQL walks the tree to find matches in roughly log₂(N) steps. For 1 million rows, that’s about 20 steps instead of 1 million.

Critical rule: indexes help when you filter by equality (=), range (<, >, BETWEEN) or prefix match (LIKE 'term%'). They do not help with LIKE '%term%' (leading wildcard) or function calls like WHERE DATE(created_at) = '2025-01-01'.

Finding slow queries with EXPLAIN

Before adding indexes, find your slowest queries. Enable MySQL’s slow query log:

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

Then run mysqldumpslow -s t /var/log/mysql/slow.log to see the top slow queries. Next, prefix any suspect query with EXPLAIN to see how MySQL runs it:

EXPLAIN SELECT * FROM products WHERE category_id = 12 AND price < 100;

Watch the type column:

• ALL — full table scan (worst).
• range or ref — using an index (good).
• const — primary-key lookup (best).

Also check rows — the number scanned. If it approaches the table total, add an index.

Single vs composite indexes (and the covering index)

Single-column indexes are for columns you filter on frequently:

CREATE INDEX idx_category ON products (category_id);

Composite (multi-column) indexes are far more efficient than separate indexes when you filter on several columns. MySQL can use only one index per table reference, but a composite index spans several columns:

CREATE INDEX idx_cat_price ON products (category_id, price);

Ordering rule: put the most selective column first (the one that filters the most rows). Typically category_id cuts 50,000 → 5,000, then price cuts to 200. Reverse them and the index is less efficient for range queries on price.

Covering indexes (the holy grail): if your query only needs columns that are in the index, MySQL answers it entirely from the index — never touching the table data. Extremely fast.

Real example: ecommerce product filtering — before and after

An online store with 50,000 products had a search page filtering by category, brand and price range. The original query:

SELECT * FROM products
WHERE category_id = 5 AND brand_id = 8 AND price BETWEEN 20 AND 100;

Without an index: full table scan — 2.3 seconds, 50,000 rows scanned. After analysis, they added a composite index:

CREATE INDEX idx_filters ON products (category_id, brand_id, price);

Result: the same query ran in 80ms (96% faster), scanning only 312 rows. Page load fell from 3.2s to 0.6s.

Indexing for WordPress (if you must stay)

WordPress is notorious for slow queries because of its generic schema. The wp_postmeta table stores every custom field as a key-value pair, producing millions of rows. You can add custom indexes for common queries:

ALTER TABLE wp_postmeta
ADD INDEX idx_meta_key_value (meta_key(20), meta_value(50));

But even with indexing, WordPress still carries wp_query overhead. Custom PHP apps designed with a proper schema and indexes will always be faster.

Index maintenance — when to add, when to drop

• Add indexes on columns used in WHERE, JOIN, ORDER BY and GROUP BY.
• Drop indexes on rarely used or very low-cardinality columns (e.g. a status column with two values). Every index slows INSERT, UPDATE and DELETE because MySQL must update the index too.
• Monitor index usage with SHOW INDEX FROM table; and SELECT * FROM sys.schema_unused_indexes; (MySQL 8.0).

Tools for index analysis

• EXPLAIN — built in; always start here.
• MySQL Workbench — visual EXPLAIN and a performance dashboard.
• pt-query-digest (Percona Toolkit) — analyses the slow log and suggests indexes.
• phpMyAdmin — a “Profiling” feature for query execution details.

Client case study: real-estate platform

A real-estate site had 200,000 property listings. Users searched by city, price range, bedrooms and property type. The search page took 4–6 seconds because the query scanned the whole table every time.

Solution: after analysing the slow log and using EXPLAIN, we added a composite index:

CREATE INDEX idx_search ON listings (city, property_type, bedrooms, price);

We also rewrote the query to use a covering index — selecting only the needed columns (id, title, price, thumbnail) instead of SELECT *.

Results:

• Query time: 5.2 seconds → 90ms.
• Server CPU load dropped 70%.
• Page load: 6.5s → 1.2s (including frontend).
• User engagement rose 34% because people stopped abandoning slow searches.

Common mistakes and how to fix them

• Indexing every column — too many indexes slow writes. Index only columns in WHERE, JOIN or ORDER BY.
• Using functions on indexed columns — WHERE DATE(created_at) = '2025-01-01' ignores the index. Instead use WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.
• Leading wildcard in LIKE — WHERE name LIKE '%widget%' never uses the index. Use a FULLTEXT index for partial matches.
• Not using EXPLAIN before and after — always run EXPLAIN before adding an index and again after, to confirm the improvement.

The trade-off

Indexes aren’t free: they take disk space and slightly slow INSERT/UPDATE because the index must be maintained. So index deliberately — the columns you actually query — rather than everything. On read-heavy workloads the speed win dwarfs the write cost.

Sources & further reading

• MySQL — How indexes are used
• MySQL — Optimizing queries with EXPLAIN
• MySQL — Optimization and indexes

Related services

• Migrate WordPress to custom PHP — Where a clean schema pays off.
• What makes a website load fast — Backend speed is half the battle.
• Lighthouse 100 — Fast queries feed a fast page.

Frequently asked questions

What does a MySQL index do?

It’s a sorted lookup structure that lets MySQL jump straight to matching rows instead of scanning the whole table, turning an O(n) scan into O(log n).

How do I find missing indexes?

Enable the slow query log, then run EXPLAIN on slow queries. A type of ALL means a full table scan and a missing index.

Do indexes slow down writes?

Slightly — every INSERT, UPDATE and DELETE must also update the index. So index only the columns you actually query.

What is a composite index?

A single index spanning multiple columns. Put the most selective column first so it filters the most rows.

What is a covering index?

An index that contains every column a query needs, so MySQL answers the query from the index alone without touching the table — extremely fast.

How much does a custom PHP site cost?

Three flat-fee packages: a business pro site at $1,750, an ecommerce site at $5,600, and SaaS / web apps at $10,000 — all one-time, no monthly fees.

Can you optimise an existing slow database?

Yes — I can analyse your slow logs, add the right indexes and often cut page load times 50% or more, on WordPress or a custom app.