Database Indexing for Scale: Optimizing WordPress Query Performance

Previously in this course, we covered Advanced Custom Database Tables to structure our data and Data Access Objects Pattern to interact with it safely. While those lessons established the "how" of data storage, this lesson focuses on the "how fast" by teaching you to profile and refine your indexing strategy under load.

As your Knowledge Base plugin grows, simple SELECT queries that run in milliseconds on your local machine will eventually choke on production datasets with thousands of rows.

The First Principle: Indexing is a Trade-off

An index is a sorted data structure (typically a B-Tree) that allows the database engine to locate rows without scanning every record. Think of it like the index at the back of a textbook: you don't read every page to find a topic; you jump straight to the relevant section.

However, indexes aren't free. Every time you INSERT, UPDATE, or DELETE a row, the database must also update the index. Over-indexing slows down write operations, while under-indexing kills read performance. Our goal is to achieve "Goldilocks" indexing: just enough to make reads lightning-fast without crippling write throughput.

Analyzing Query Performance with EXPLAIN

Before you add an index, you must verify that your query is actually the bottleneck. The EXPLAIN keyword is your most powerful tool. By prepending EXPLAIN to any SELECT statement in your MySQL client, you receive a breakdown of how the engine intends to execute your query.

Let’s look at a common Knowledge Base query: searching for articles by a specific category_id and status.

SQL
EXPLAIN SELECT * FROM wp_kb_articles WHERE category_id = 5 AND status = 'published';

When you run this, pay close attention to three specific columns:

• type: If you see ALL, the database is performing a "Full Table Scan." This is your primary signal that an index is missing.
• key: Shows which index the engine is using. If this is NULL, no index was utilized.
• rows: An estimate of how many rows the engine must examine. A large number here relative to your total dataset size indicates inefficiency.

Worked Example: Optimizing a Search Query

Imagine our wp_kb_articles table has 50,000 entries. We have a dashboard widget that fetches the latest 10 published articles for a specific category.

Without an index, the database must scan all 50,000 rows. If we add a composite index on (category_id, status), the database jumps directly to the relevant subset.

1. The "Before" Plan: If type is ALL, the query is hitting the disk for every row.

2. The Optimization: We implement a migration to add the composite index:

PHP
global $wpdb;
$table_name = $wpdb->prefix . 'kb_articles';
$wpdb->query("CREATE INDEX idx_cat_status ON $table_name (category_id, status)");

3. The "After" Plan: Run EXPLAIN again. You should now see:

• type: ref or const.
• key: idx_cat_status.
• rows: Significantly lower, likely close to the actual number of published articles in that category.

Hands-on Exercise

1. Identify a slow query: In your local development environment, use the SAVEQUERIES constant in wp-config.php to log all queries. Identify one query that runs on your Knowledge Base table.
2. Run EXPLAIN: Copy that query and run it via phpMyAdmin or your terminal MySQL client with the EXPLAIN prefix.
3. Apply an index: Based on the EXPLAIN output, create an index on the column(s) used in your WHERE or JOIN clauses.
4. Verify: Re-run the EXPLAIN query and observe the change in the rows count and type.

Common Pitfalls

• The Leftmost Prefix Rule: If you create a composite index on (category_id, status), searching by category_id will use the index, but searching by status alone will not. Always order your composite index columns from the most selective (the one that filters out the most data) to the least selective.
• Functions on Columns: Never use functions in your WHERE clause if you want to use an index. WHERE YEAR(created_at) = 2023 prevents the index on created_at from being used. Instead, use a range: WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'.
• Index Bloat: Do not index every single column. Only index columns used in WHERE, JOIN, or ORDER BY clauses.

Recap

Indexing is a balancing act between read speed and write overhead. By mastering EXPLAIN, you move from guessing why a query is slow to having concrete evidence of execution paths. Always favor composite indexes for multi-column filters and ensure your query logic doesn't inadvertently bypass the database's ability to use your indexes.

For further reading on how different database architectures handle these concepts, you might find Indexing Strategy for App Developers: Stop Slow Queries a useful companion to your toolkit.

Up next: Sanitization Pipelines — we'll move from database performance to ensuring the data entering that database is clean, safe, and schema-compliant.