Category: MySQL

MySQL’s optimiser hints (available since 5.7, extended in 8.0) let you override the query planner’s decisions without changing session variables or schema — they are embedded directly in the query with /*+ HINT */ syntax and affect only that specific statement. For WordPress performance work, hints are invaluable when the optimiser consistently makes a bad…

MySQL 9.0 (GA 2024) introduces two headline features: the VECTOR data type for storing and querying high-dimensional embeddings (enabling semantic search directly in the database), and JavaScript stored programs via the MLE (Multilingual Engine) component. For WordPress developers, the VECTOR type opens the door to AI-powered search without an external vector database.

GTID (Global Transaction Identifiers) replication, available since MySQL 5.6, makes replica promotion and failover deterministic — each transaction has a unique ID that replicas track, so a replica can be promoted to primary without manually calculating binary log positions. For WordPress high-availability setups, GTID replication combined with a monitoring script and WordPress’s HyperDB drop-in provides…

InnoDB’s row-level locking is what makes WordPress’s MySQL backend safe for concurrent writes, but it also introduces deadlocks and long-running lock waits that show up as Lock wait timeout exceeded errors in busy WooCommerce stores. Understanding the difference between record locks, gap locks, and next-key locks — and knowing when each is acquired — is…

MySQL 8.0.4+ ships the JSON_TABLE() function — a table-valued function that converts a JSON document (or array) stored in a column into a virtual relational table you can join, filter, and aggregate just like a regular table. This is invaluable when WordPress post meta or WooCommerce order meta stores arrays or objects as JSON and…

WordPress stores category and comment hierarchies with a parent column, but querying the full tree at arbitrary depth with a single SQL statement requires recursive CTEs — available in MySQL 8.0+ and MariaDB 10.2+. A recursive CTE consists of an anchor member (the root rows) and a recursive member (each subsequent level) joined with UNION…

MySQL’s built-in FULLTEXT index on InnoDB tables provides fast, relevance-ranked text search without an external engine. The default natural language mode ranks results by TF-IDF frequency; Boolean mode adds operator-based filtering (+must -exclude "exact phrase" word*). Understanding how to tune the minimum word length, stopwords, and the query expansion modes turns MATCH()...AGAINST() from a blunt…

On high-traffic WordPress sites, the cost of establishing a new TCP connection to MySQL for every PHP request becomes a bottleneck long before the query itself is slow. ProxySQL acts as a transparent connection pool in front of MySQL: PHP connects to ProxySQL on port 6033, and ProxySQL multiplexes those short-lived PHP connections into a…

MySQL’s slow query log records every query that takes longer than long_query_time seconds. For WordPress, it is the definitive tool for finding the N+1 queries, missing indexes, and poorly-written plugin queries that degrade performance at scale — before they become production incidents.