Sanjeev Sabhlok's notes on technology, hardware, gardening

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.

STOPGAP SOLUTION

  1. Go to myphyphadmin > relevant database > wp_postmeta table
  2. In the ‘meta_key’ row, click the icon with ‘lists’ (i.e. browse distinct values upon mouseover)
  3. Sort by rows (descending)
  4. This gives you the names of meta_keys that are hogging your space

SQL query:

SELECT COUNT( * ) AS `Rows` , `meta_key`
FROM `wp_sys1_postmeta`
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

Use this:

DELETE FROM wp_postmeta WHERE `meta_key` LIKE ‘%blogger%’;

 

Print Friendly, PDF & Email

sabhlok

View more posts from this author

Leave a Reply

Your email address will not be published. Required fields are marked *