September 5, 2015
Clean up wp_postmeta table in the WordPress database. It hogs a lot of SQL memory.
postmeta seems to collect on its own. I had >200k postmeta records. Ridiculous.
Unfortunately, with very little idea of what these metas do, I had to adopt a conservative approach and not delete the whole table.
- Go to myphyphadmin > relevant database > wp_postmeta table
- In the ‘meta_key’ row, click the icon with ‘lists’ (i.e. browse distinct values upon mouseover)
- Sort by rows (descending)
- This gives you the names of meta_keys that are hogging your space
SELECT COUNT( * ) AS `Rows` , `meta_key`
GROUP BY `meta_key`
ORDER BY `Rows` DESC
LIMIT 30 , 30
Now identify meta_keys that are linked with defunct plugins and get rid of them by going back to the table view and running SQL query of the type:
DELETE FROM wp_postmeta WHERE meta_key = ‘_yoast_wpseo_linkdex’;
And so on.
Once you’ve deleted a lot of obviously useless metas, then select rows with overhead and optimize.
DELETE MULTIPLE ROWS
DELETE FROM wp_postmeta WHERE `meta_key` LIKE ‘%blogger%’;