DEV Community

Cover image for Missing Database Indexes Are Killing Your Page Speed
Dario Zadro
Dario Zadro

Posted on

Missing Database Indexes Are Killing Your Page Speed

#mysql #webdev #performance #wordpress

I provide SEO. I also build apps. And the performance problem I see most often has nothing to do with meta tags, Core Web Vitals scores, or caching plugins.

It's missing database indexes.

Everyone chases the green scores. Images get lazy-loaded. Fonts get preloaded. Someone installs a caching plugin and calls it done. Green across the board. Screenshot posted on LinkedIn. Everybody claps.

But the uncached request still exists. The logged-in user still exists. The admin dashboard still exists. The report page pulling from six tables still exists. Caching hides the problem. It doesn't fix it.

The Fast Diagnostic

Enable MySQL's slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SHOW VARIABLES LIKE 'slow_query_log_file';
Enter fullscreen mode Exit fullscreen mode

Reproduce the slow page. Check the log. Then run EXPLAIN on whatever shows up:

EXPLAIN
SELECT * FROM parent_table p
JOIN child_table c ON c.parent_id = p.id
WHERE p.account_id = 42 AND p.status = 1;
Enter fullscreen mode Exit fullscreen mode

Four columns tell the whole story: type, key, rows, and Extra.

ALL in type with NULL in key is the thing you don't want to see. Especially inside a subquery running once per parent row. Not mysterious. Just waste.

The Fixes

Foreign key columns — index every column used in a JOIN:

ALTER TABLE child_table ADD INDEX idx_parent_id (parent_id);
Enter fullscreen mode Exit fullscreen mode

Composite indexes — column order matters. Most-filtered column goes first:

ALTER TABLE parent_table
ADD INDEX idx_account_status_created (account_id, status, created_at);
Enter fullscreen mode Exit fullscreen mode

JSON extractions — can't index a computed expression directly. Use a generated column:

ALTER TABLE event_log
ADD COLUMN event_source VARCHAR(100)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.source'))) VIRTUAL,
ADD INDEX idx_event_source (event_source);
Enter fullscreen mode Exit fullscreen mode

Don't overdo it — every index slows writes. Index what you actually query. That's it.

WordPress Specifically

WordPress runs 42.5% of the web (W3Techs, March 2026). And wp_postmeta does not scale gracefully under plugin pressure. Don't even get me started on RankMath's database bloat.

The Index WP MySQL For Speed plugin by Ollie Jones is a solid first move for standard installs. Heavily customized sites need a manual EXPLAIN review.

Real Result

A dashboard recently: 3+ seconds to load, 120,000 records, correlated subqueries hitting unindexed join columns. Thousands of row reads per load, every single request.

Three index statements. Instant. Same server, same code, same database.

Check the DB structure before you blame the host or code. The answer is usually closer than you think.

Full breakdown on the Zadro Web blog: https://zadroweb.com/blog/database-indexes-slow-website/

Top comments (0)