Category: MySQL

The MySQL Performance Schema (P_S) is an instrumentation framework built into the server that records query execution times, lock waits, I/O waits, and memory usage at microsecond granularity — without the overhead of general query logging. For WordPress performance tuning, P_S provides the ground truth for questions like “which query spends the most time waiting…

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…