MySQL and the WordPress database

Point-in-time recovery for WordPress with the MySQL binary log A nightly backup protects against losing everything, but not against losing a day. When a faulty plugin update or a wrong DELETE destroys data at 15:42, restoring last night’s dump also throws away every order and comment since midnight. MySQL’s binary log records every change; with…

InnoDB buffer pool for WordPress: size it, then check that it works The InnoDB buffer pool is MySQL’s memory cache for table and index pages. When the working set of a WordPress database (the posts, the meta, the options, the WooCommerce lookup tables that are actually read) fits into it, queries are served from RAM.…

“Too many connections”: PHP-FPM, MySQL and when ProxySQL helps PHP has no connection pool in the usual sense: every request that WordPress handles opens its own MySQL connection and closes it at the end. Under load, the number of connections equals the number of busy PHP-FPM workers, from every web server. When that number exceeds…

MySQL generated columns: index values that are computed or inside JSON A query that filters on LOWER(email), on DATE(created_at) or on a key inside a JSON document cannot use a normal index: the index stores the column, not the result of the expression. A generated column stores (or computes) that expression as a column of…

Query Monitor: find what makes a WordPress page slow A slow page has a cause somewhere between the database, PHP code, hooks and external HTTP requests. Query Monitor is a free debugging plugin that shows all of this for the current request in a panel under the admin bar: every database query with its time…

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…

MySQL triggers as an audit log for WordPress options Some changes in a WordPress database come from nowhere: the admin email is suddenly different, users_can_register is on, a plugin appears in active_plugins. A logging plugin records what happens through WordPress, but not what a script, a database tool or an attacker with database access changes…

A JSON column for flexible data in a WordPress plugin table Some data has no fixed shape. The answers to a quiz depend on the questions: a choice here, a free text there, a rating somewhere else. Stored as post meta, one response becomes 50 rows in wp_postmeta. A custom table with a JSON column…

Foreign keys in custom WordPress plugin tables WordPress itself uses no foreign keys: the relation between a post and its meta rows exists only in PHP code. A plugin with its own tables can do better and let the database keep the data consistent, for example delete the items of an order together with the…