Wordpress Database Optimization

Heya,

The error “The table does not support optimize” typically occurs when the database table engine is InnoDB, which does not directly support the OPTIMIZE TABLE command like MyISAM. Instead, you need to use alternative methods to optimize and clean up your wp_postmeta table.

You can try using wp-cli to optimize all your DB tables. Run the following command to optimize all database tables, including wp_postmeta:

wp db optimize

If wp_postmeta uses the InnoDB engine, the optimization process may not reclaim space directly. Instead, use alternative methods (see Rebuild Table below).

2. Clean Orphaned Post Meta

You can use WP-CLI commands to clean orphaned post meta. While WP-CLI doesn’t have a direct command for orphaned meta, you can use a plugin like WP-Sweep, which provides WP-CLI support.

Install WP-Sweep:

wp plugin install wp-sweep --activate

Then, run the sweep command to clean orphaned meta:

wp sweep orphaned_postmeta

. Export and Analyze Large Tables

If the wp_postmeta table is too large, export it for offline analysis:

wp db export wp_postmeta_backup.sql --tables=wp_postmeta

You can analyze and clean the exported data locally using SQL tools or scripts

If you have WP-CLI installed, you can use the following command to remove orphaned metadata. These are entries that are not associated with any existing posts:

wp postmeta delete --orphaned

If manual methods seem too complex, or if you want additional optimization features, you can use some advanced database optimization plugins like:

• WP-Optimize: In addition to cleaning the database, it can optimize tables. Be sure to enable “Clean all post revisions” and “Clean postmeta” options.
• Advanced Database Cleaner: Helps with identifying and removing unused metadata, transient options, and more.
• WP-Sweep: It’s another plugin that can clean up your WordPress database, including postmeta.

This command deletes any postmeta entries that do not have a corresponding post.

You can also check the following articles:

Also you can run a script like the MySQL tunner to recommend optimised values for MySQL/MariaDB. There is a mini-tutorial which you can check here:

https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

https://www.digitalocean.com/community/tutorials/how-to-optimize-wordpress-on-ubuntu

https://www.digitalocean.com/community/tutorials/how-to-speed-up-wordpress-asset-delivery-using-digitalocean-spaces-cdn

https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql-on-ubuntu

Hope that this helps! Happy holidays!