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

A proper index can turn a 2‑second product search into 30ms – a 98% improvement. Yet most WordPress sites run only on default indexes, and many custom PHP apps are built without any indexing strategy. This guide teaches you how to design indexes that make your database scream.

Why Indexing Matters for Web Performance

When a user visits a page that queries a database (product listings, search results, user profiles), MySQL must find the relevant rows. Without indexes, MySQL performs a full table scan – reading every row until it finds matches. For a table with 100,000 rows, a full scan takes 200‑500ms. With 1,000 concurrent users, that becomes seconds of delay, and your server CPU maxes out.

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 structure. When you run a query with a WHERE clause on an indexed column, MySQL traverses the tree to find the matching values in roughly log₂(N) steps. For 1 million rows, that’s about 20 steps instead of 1 million scans.

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

Identifying Slow Queries – Using EXPLAIN

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

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Then run mysqldumpslow -s t /var/log/mysql/slow.log to see the top slow queries.

Next, prepend EXPLAIN to any suspicious query to see how MySQL executes it. For example:

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

Look at 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 of rows scanned. If it’s close to the table total, add an index.

Practical Indexing Strategy – Single vs Composite Indexes

Single‑Column Indexes

Use when you frequently filter on one column.

CREATE INDEX idx_user_id ON orders (user_id);

Composite (Multi‑Column) Indexes

When you filter on multiple columns, a composite index is far more efficient than separate indexes. MySQL can use only one index per table reference, but a composite index covers multiple columns.

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

Order rule: Put the most selective column first (the one that filters out the most rows). Usually category_id reduces 50,000 → 5,000, then price reduces to 200. If you reversed (price, category_id), the index would be less efficient for range queries on price.

Covering Indexes (The Holy Grail)

If your query only needs columns that are in the index, MySQL can answer the query entirely from the index – no need to touch the table data. This is extremely fast.

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

Real Example: Ecommerce Product Filtering – Before & After

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

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

Without index: full table scan – 2.3 seconds, 50,000 rows scanned.

After analysis: They added a composite index:

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

Result: The same query ran in 80ms (96% improvement), scanning only 312 rows. Page load time dropped from 3.2 seconds to 0.6 seconds.

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, leading to millions of rows. You can add custom indexes to improve common queries:

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

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

Maintaining Indexes – When to Add, When to Remove

• Add indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY.
• Remove indexes on columns that are rarely used or have very low cardinality (e.g., a `status` column with only two values). Each index slows down 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 Indexing Analysis

• EXPLAIN – built‑in, always start here.
• MySQL Workbench – visual EXPLAIN and performance dashboard.
• pt‑query‑digest (Percona Toolkit) – analyzes slow query log and suggests indexes.
• phpMyAdmin – “Profiling” feature to see query execution details.

Client Case Study: Real Estate Platform

A real estate website had 200,000 property listings. Users searched by city, price range, bedrooms, and property type. The search page took 4–6 seconds to load because the query scanned the entire table each time.

Solution: After analyzing slow logs and using EXPLAIN, we added a composite index:

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

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

Results:

• Query time: 5.2 seconds → 90ms.
• Server CPU load dropped by 70%.
• Page load time: 6.5s → 1.2s (including frontend).
• User engagement increased by 34% because users didn't abandon slow searches.

Common Mistakes and How to Fix Them

Mistake 1: Indexing Every Column

Problem: Too many indexes slow down writes. Fix: Only index columns that appear in WHERE, JOIN, or ORDER BY.

Mistake 2: Using Functions on Indexed Columns

Bad: WHERE DATE(created_at) = '2025-01-01' – ignores index on created_at. Good: WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.

Mistake 3: Leading Wildcard LIKE

Bad: WHERE name LIKE '%widget%' – never uses index. Good: Use a full‑text search index for partial matches.

Mistake 4: Not Using EXPLAIN Before and After

Always run EXPLAIN before adding an index, and again after, to verify the improvement.

Advanced: Automating Index Suggestions

For MySQL 8.0, you can enable the performance schema and use the sys schema to find missing indexes:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

For older versions, percona-toolkit’s pt-index-usage analyzes slow logs and suggests indexes.

Ready to Optimize Your Database?

I build custom PHP applications with proper database indexing from day one. Whether you have a slow WordPress site or a custom app with query bottlenecks, I can analyze your slow logs, add the right indexes, and often cut page load times by 50% or more.

Let’s talk about your database performance issues. I’ll provide a free assessment of your slowest queries.

All performance figures from real client audits using MySQL 8.0 on Hostinger VPS. Your results may vary based on table size and server hardware.