WordPress Database Optimizations
delete from wp_usermeta where meta_value = ''; delete from wp_postmeta where meta_value = ''; delete from wp_options where option_value = '';
Because why not?
Tag Archives: performance
Cleaning Up Bot Registrations in WooCommerce
…or Optimizing Slow Sub-Queries in WordPress
Bot registrations are a nuisance in many WooCommerce sites. Cleaning them up seems to be a trivial task: just delete all users without a placed order from a month ago and backwards.
select * from wp_users where user_registered < "2020-07-01 00:00:00"; 47665 rows in set (0.06 sec) select meta_value from wp_postmeta where meta_value = '_customer_user'; 51253 rows in set (0.44 sec)
Okay, so we almost 50 thousand customers and a bit over 50 thousand orders.
The query to delete all the users that have no order is seemingly a simple one:
delete from wp_users where user_registered < "2020-07-01 00:00:00" and id not in (select meta_value from wp_postmeta where meta_key = '_customer_user' group by meta_value);
Great. Yet there's a huge issue: Query OK, (59 min 7.22 sec)
Ooomph! This won't effing do!
Testing Race Conditions in WordPress
WordPress is not thread-safe.
I’ve spoken about this, and even started work on a plugin called WP_Lock that will aim to introduce some thread-safety into core to address the occasional TOCTOU bug under high load (and concurrency). For example ticket #44568 is an easy-to-reproduce complaint about concurrent REST API access 😉
If you thought writing thread-safe code in WordPress plugins is hard, unit testing the code for concurrency issues is even harder. One of the ways I found works best is by utilizing the PCNTL module in PHP to fork and test critical sections.
Large and Laggy Gravity Forms
Over at GravityView, I have the pleasure of working on Gravity Forms-related things and sometimes we have a performance issue on our hands. This time it was an issue with a form that has about 15 visible conditional fields, about 300 (yes, three, zero, zero, three hundred :)) hidden calculation fields and a product total at the end.
This form was very laggy in the browser. Any interaction took around 3 seconds to propagate through, blocking the rendering thread completely.
Lighthouse – WordPress Performance Plugin Review
Like many rants, this one starts with a promise. One made by the developers of Lighthouse on their marketing page:
…optimize MySQL tables by removing all unused table space (both MyISAM and InnoDB)…
All unused table space for InnoDB via a PHP plugin? Suspicious, I got my hands on a copy of the plugin to see how it accomplishes this claimed feat. Well, obviously it didn’t reclaim all InnoDB tablespace, just some of it using the OPTIMIZE TABLE method (only effective when innodb_file_per_table is set to “on” which is enabled by default in MySQL 5.6.6 and higher). The ibdata file remained untouched.
Okay, no problem, not unexpected. But then I read into some of the other code and a second, much more substantial promise:
A zero-footprint tuning plugin…
…I became pretty angry. I’ve always been a skeptic of magic optimization plugins, and Lighthouse was unfortunate enough to get my review.