Query optimization

MySQL partitioning for large WordPress log tables Partitioning splits a table into separate physical parts by a rule, for example one part per month, while queries still see one table. For WordPress, the useful case is not wp_posts or wp_postmeta, but the large append-only tables that plugins create: logs, statistics, events. There, partitions make old…

A WordPress performance audit checklist Performance work on WordPress sites often starts with a caching plugin and a vague feeling, and ends without knowing what helped. An audit turns it around: measure first, go through the layers from the server to the browser, write down what is wrong, and fix the biggest problem first. This…

MySQL window functions and CTEs for WordPress reports An editorial report often asks questions like “the top three posts of each category” or “the running total of comments per month”. With plain GROUP BY such questions need several queries and PHP loops. Window functions (ROW_NUMBER(), RANK(), SUM() OVER) and common table expressions (WITH) answer them…

Full-text search in WordPress with MySQL MATCH AGAINST The built-in WordPress search runs LIKE '%word%' on the title, excerpt and content of every post. That reads the whole table for each search and sorts the results only roughly by relevance. On a site with tens of thousands of posts, a MySQL FULLTEXT index answers the…

Optimize WordPress database tables: cleanup, ANALYZE and OPTIMIZE Over the years a WordPress database collects revisions, expired transients, trashed posts and orphaned meta rows, and the large tables (wp_posts, wp_postmeta, wp_options) get fragmented. The MySQL optimizer also works with index statistics that can be outdated after big imports or deletions. Three commands help: ANALYZE TABLE…

MySQL primary-replica replication with GTID for WordPress When one database server cannot keep up with the reads of a busy WordPress site, a replica takes part of the load: it receives every change from the primary and can answer SELECT queries. With GTID (global transaction identifiers) the replica tracks its position by transaction, not by…

Faster WooCommerce product archives: lookup tables and cached queries A shop category with a few thousand products, price sorting and attribute filters can take seconds to render. WooCommerce has its own tables to make these queries cheaper, the product meta lookup table and the attributes lookup table, but they help only when they are filled…

Enable the MySQL slow query log and analyze WordPress database performance Without logging, slow queries are invisible: the site is slow, but it is not clear which query, from which plugin, is to blame. MySQL’s slow query log records every statement that runs longer than a threshold. It can be enabled on a live server…

Create a custom database table in a WordPress plugin with dbDelta wp_postmeta stores any key-value data, but for high-volume or relational plugin data (logs, statistics, bookings) it becomes a bottleneck: every value is a LONGTEXT in one huge table, and filtering by several values means several joins. A custom table gives you your own columns…