Advanced SQL for WordPress

MySQL optimizer hints: steer a single WordPress query without changing the server Sometimes the MySQL optimizer picks a bad plan for one query: it uses an index merge that reads far more rows than needed, chooses the wrong index for a meta query, or joins tables in an order that explodes the intermediate result. Changing…

MySQL 8.4 LTS and 9.x for WordPress hosts: what changes, what to wait for MySQL changed its release model in 2024: Long-Term Support releases (8.4, released April 2024) for production, and Innovation releases (9.0, 9.1, …) every few months with new features and a short support window. MySQL 8.0 reached its end of life in…

InnoDB locks and deadlocks in WordPress: read the error, fix the order “Deadlock found when trying to get lock; try restarting transaction” appears in a WordPress log mostly on busy WooCommerce shops and in plugins that update counters or custom tables in transactions. InnoDB detects the deadlock and rolls back one of the transactions, so…

MySQL JSON_TABLE: turn JSON into rows you can join and aggregate Data in a JSON column is easy to store and awkward to report on: a JSON array of order lines from an external system cannot be summed with SUM() or joined to wp_posts as it is. JSON_TABLE() turns a JSON document into a temporary…

Recursive CTEs: query category trees and page hierarchies in MySQL WordPress stores hierarchies as parent pointers: a page has a post_parent, a category a parent in wp_term_taxonomy. Finding all descendants of an item needs one query per level in PHP, or a function like get_term_children() that loads the whole taxonomy into memory. A recursive common…

MySQL FULLTEXT search for WordPress: natural language, boolean mode and relevance WordPress search is a LIKE '%term%' on title, excerpt and content: no ranking beyond “title matches first”, no stemming, and a full table scan on every search. A FULLTEXT index on wp_posts gives MySQL a word index with relevance scores, and boolean mode adds…

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…

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…

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…