Advanced SQL for WordPress

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…

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…

Migrate WooCommerce orders to High-Performance Order Storage (HPOS) High-Performance Order Storage moves WooCommerce orders out of wp_posts and wp_postmeta into their own tables: wc_orders, wc_order_addresses, wc_order_operational_data and wc_orders_meta. It appeared as an opt-in feature in WooCommerce 7.1, and with WooCommerce 8.2 (October 2023) it becomes the default for new stores. The reason is performance: in…

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.

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…

Understand how MySQL indexes work and learn to add the right indexes to WordPress custom tables and meta queries to eliminate slow full-table scans.

Create MySQL stored procedures for batch WordPress database operations like orphan cleanup and category stats, and call them from wpdb.