Query optimization

PHP generators and yield: memory-efficient data processing in WordPress A generator is a function with yield: instead of building a full array and returning it, it hands out one value at a time and pauses until the caller asks for the next one. For WordPress jobs over large data sets (going through all products, exporting…

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…

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 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…

InnoDB buffer pool for WordPress: size it, then check that it works The InnoDB buffer pool is MySQL’s memory cache for table and index pages. When the working set of a WordPress database (the posts, the meta, the options, the WooCommerce lookup tables that are actually read) fits into it, queries are served from RAM.…

“Too many connections”: PHP-FPM, MySQL and when ProxySQL helps PHP has no connection pool in the usual sense: every request that WordPress handles opens its own MySQL connection and closes it at the end. Under load, the number of connections equals the number of busy PHP-FPM workers, from every web server. When that number exceeds…

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…

Query Monitor: find what makes a WordPress page slow A slow page has a cause somewhere between the database, PHP code, hooks and external HTTP requests. Query Monitor is a free debugging plugin that shows all of this for the current request in a panel under the admin bar: every database query with its time…