Question

Wordpress Database Optimization

Hello Team,

We need to optimize the Postmeta tables. Since it has 2 GB. We tried to optimize the above tables. But it says like https://prnt.sc/8qE8ye8aNJqO. Please let me know the steps to resolve it. I have tried with WP optimizer Plugin and other plugins which are related to database optimizer. But still it says the same issue.

Thanks


Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

KFSys
Site Moderator
Site Moderator badge
December 25, 2024

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

alexdo
Site Moderator
Site Moderator badge
December 29, 2024

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!

Become a contributor for community

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

DigitalOcean Documentation

Full documentation for every DigitalOcean product.

Resources for startups and SMBs

The Wave has everything you need to know about building a business, from raising funding to marketing your product.

Get our newsletter

Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

New accounts only. By submitting your email you agree to our Privacy Policy

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Get started for free

Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

*This promotional offer applies to new accounts only.