MySQL and the WordPress database

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: what WordPress developers need to know MySQL changed its release model: since 2023 there are Long-Term Support releases (8.4, released in April 2024) for production, and Innovation releases (9.0, 9.1 and so on, roughly every quarter) with new features, each supported only until the next one. At the same time…

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…

MySQL Clone Plugin: provision a staging database from production The MySQL Clone Plugin (MySQL 8.0.17+) copies a running MySQL instance at the physical level: the InnoDB data files are streamed to another server while the source keeps working. For a database of tens of gigabytes this is much faster than mysqldump and a restore, which…

MySQL replication with GTID for WordPress: set up, monitor, fail over A replica of the WordPress database gives a second copy that is seconds behind, a place to run heavy reports and backups without touching the primary, and a server to switch to when the primary fails. GTID-based replication (global transaction identifiers) makes the last…

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…