MySQL and the WordPress database

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…

Back up and restore a WordPress MySQL database with mysqldump and cron A backup is only as good as the restore. This article is about the database part: a daily compressed dump with a retention policy, a copy outside the server, a dedicated backup user, and the restore procedure, tested before it is needed. For…

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…

Run custom SQL queries in WordPress with $wpdb->prepare() and get_results() WP_Query covers most needs, but some queries it cannot express or makes inefficient: aggregations with GROUP BY, joins across several tables, bulk updates. For those, WordPress gives the $wpdb object, a direct connection to the database, with $wpdb->prepare() for safe parameters.

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…

Remove WordPress database bloat including orphaned postmeta rows, unlimited post revisions, expired transients, and spam comments with safe SQL queries and WP-CLI commands.