{"id":1849,"date":"2019-02-14T13:57:20","date_gmt":"2019-02-14T21:57:20","guid":{"rendered":"https:\/\/codedcommerce.com\/?p=1849"},"modified":"2024-07-25T15:55:31","modified_gmt":"2024-07-25T22:55:31","slug":"cleaning-up-your-woocommerce-database","status":"publish","type":"post","link":"https:\/\/codedcommerce.com\/cleaning-up-your-woocommerce-database\/","title":{"rendered":"Cleaning up your WooCommerce database"},"content":{"rendered":"\n

There\u2019s lots you can do to improve database (DB) performance, but each change comes with risks. Always thoroughly test changes in a development environment first<\/strong> before making permanent database changes in production.<\/p>\n\n\n\n

Growth in data requires a growth in hosting to handle the scale of database size and queries.<\/h2>\n\n\n\n

There\u2019s only so much you can clean-up practically. When you reach this point, hosting with solid managed hosting like Pantheon.io<\/a> or custom cloud infrastructure like AWS<\/a> can offer a great deal of scale, costing on the magnitude of $5k\/mo in huge DB servers if it becomes too important to have all the historic data while keeping things fast. Those costs are out of reach for most of us, so minding your DB health is critical as you grow so you don’t have to spend huge on hosting before it’s truly necessary to do so.<\/p>\n\n\n\n

There may even come a point where it makes sense to shard the data (i.e. move historic quarters or years data over to backup environments for archival usage).<\/p>\n\n\n\n

The best solution will depend on each client’s unique needs.<\/p>\n\n\n\n

Here\u2019s my practical tips in this area:<\/p>\n\n\n\n

1) Ensure you’re using InnoDB tables.<\/h3>\n\n\n\n

To generate update queries:<\/p>\n\n\n\n

SELECT CONCAT( 'ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;' ) \nFROM INFORMATION_SCHEMA.TABLES\nWHERE ENGINE = 'MyISAM'\nAND table_schema = 'database-name-here';<\/code><\/pre>\n\n\n\n
2) Use WP Optimize plugin<\/a>.<\/h3>\n\n\n\n
This can clear out orphaned records, old drafts, etc.<\/p>\n\n\n\n
3) Use my Prune Orders plugin<\/a>.<\/h3>\n\n\n\n
This can trash all old failed, pending, cancelled orders by selected status to a specified date. If you’re working faster or prefer to use SQL here’s a useful query to select then pivot to a deletion query.<\/p>\n\n\n\n
SELECT * FROM wp_posts\nWHERE post_type = 'shop_order'\nAND post_status IN( 'wc-cancelled', 'wc-completed', 'wc-failed', 'wc-pending' )\nAND post_date < CURDATE() - INTERVAL 100 DAY\nORDER BY ID DESC;<\/code><\/pre>\n\n\n\n
4) Delete legacy post types, post meta, and user meta data.<\/h3>\n\n\n\n
For example: old order metadata, Google analytics meta, address fields, etc. To see these in order of size:<\/p>\n\n\n\n
SELECT post_type, count( post_type )\nFROM wp_posts\nGROUP BY post_type\nORDER BY count( post_type ) DESC\nLIMIT 50;\n\nSELECT meta_key, count( meta_key )\nFROM wp_postmeta\nGROUP BY `meta_key`\nORDER BY count( meta_key ) DESC\nLIMIT 50;\n\nSELECT meta_key, length( meta_value )\nFROM wp_postmeta\nORDER BY length( meta_value ) DESC\nLIMIT 50;\n\nSELECT meta_key, count( meta_key )\nFROM wp_usermeta\nGROUP BY meta_key\nORDER BY count( meta_key ) DESC\nLIMIT 50;<\/code><\/pre>\n\n\n\n
5) Examine wp_options<\/code> table health.<\/h3>\n\n\n\n
Remove old plugin cruft. To see how many MB are loading with each page request from wp_options<\/code> run this to see which rows are hogging up the most space and decide which can be removed:<\/p>\n\n\n\n
SELECT\n\t'Autoload KB' as name, ROUND( SUM( LENGTH( option_value ) ) \/ 1024 ) as size, null as autoload\n\tFROM wp_options\n\tWHERE autoload = 'yes'\nUNION\n\tSELECT 'Autoload count', count( * ), null as autoload\n\tFROM wp_options\n\tWHERE autoload = 'yes'\nUNION (\n\tSELECT option_name, length( option_value ), autoload\n\tFROM wp_options\n\tWHERE autoload = 'yes'\n\tORDER BY length( option_value ) DESC\n\tLIMIT 20\n)\nUNION (\n\tSELECT option_name, length( option_value ), autoload\n\tFROM wp_options\n\tWHERE autoload = 'no'\n\tORDER BY length( option_value ) DESC\n\tLIMIT 20\n);<\/code><\/pre>\n\n\n\n
6) Run through Query Monitor plugin or New Relic APM.<\/h3>\n\n\n\n
Use available application performance monitoring tools to analyze your staging or production environments. The best screens to test are listing and editing orders as well as all front-end pages.<\/p>\n","protected":false},"excerpt":{"rendered":"
There\u2019s lots you can do to improve database (DB) performance, but each change comes with risks. Always thoroughly test changes in a development environment first before making permanent database changes in production. Growth in data requires a growth in hosting to handle the scale of database size and queries. There\u2019s only so much you can […]<\/p>\n","protected":false},"author":1,"featured_media":1852,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52047,635718625],"tags":[],"class_list":["post-1849","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-woocommerce-guides"],"jetpack_featured_media_url":"https:\/\/codedcommerce.com\/wp-content\/uploads\/2019\/02\/kevin-ku-364843-unsplash-scaled.jpg","_links":{"self":[{"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/posts\/1849","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/comments?post=1849"}],"version-history":[{"count":0,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/posts\/1849\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/media\/1852"}],"wp:attachment":[{"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/media?parent=1849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/categories?post=1849"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codedcommerce.com\/wp-json\/wp\/v2\/tags?post=1849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}