Posted on

Cleaning up your WooCommerce Database

Glasses sitting in front of computer screens displaying code

There’s 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’s only so much you can clean-up practically. When you reach this point, hosting with solid managed hosting like Pantheon.io or custom cloud infrastructure like AWS 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.

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).

The best solution will depend on each client’s unique needs.

Here’s my practical engineer tips in this area:

  1. Ensure the DB uses InnoDB tables. To generate update queries:
    SELECT CONCAT( 'ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;' )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE ENGINE = 'MyISAM'
    AND table_schema = ‘DBNAME_HERE’;
  2. Use WP Optimize plugin to clear out orphaned records, old drafts (you can set like a 4-week retention period).
  3. Use my Prune Orders plugin to trash all old orders by selected status to a specified date (removing old failed, pending, cancelled orders).
  4. Delete all post meta of old orders that is no longer relevant, manually (i.e. old google analytics meta, address fields, etc.). To see the offenders in order of size:
    SELECT meta_key, count( meta_key )
    FROM wp_postmeta pm
    JOIN wp_posts p ON pm.post_id = p.ID
    WHERE p.post_type = 'shop_order'
    GROUP BY `meta_key`
    ORDER BY count( meta_key ) DESC
  5. Check wp_options table health and manually removing old plugin cruft. To see how many MB are loading with each page request from wp_options run this to see which rows are hogging up the most space and decide which can be removed:
    SELECT
    	'data in KiB' as name,
        ROUND( SUM( LENGTH( option_value ) ) / 1024 ) as size,
        null as autoload
    FROM wp_options
    UNION
    	SELECT 'data count', count( * ), null as autoload
        FROM wp_options
    UNION (
        SELECT option_name, length( option_value ), autoload
        FROM wp_options
        WHERE autoload = 'yes'
        ORDER BY length( option_value ) DESC
        LIMIT 20
    )
    UNION (
        SELECT option_name, length( option_value ), autoload
        FROM wp_options
        WHERE autoload = 'no'
        ORDER BY length( option_value ) DESC
        LIMIT 20
    );
  6. Run through New Relic APM. Install the site onto a free Pantheon.io dev environment, run some front and back end tests and examine the reports to find any problem PHP or SQL executions.
  7. Move products to their own tables (CAUTION: may break any plugin or code snippet that doesn’t use C.R.U.D. functions from v3.0). Check out the Custom Product Tables BETA project.
  8. Move orders to their own tables (CAUTION: may break any plugin or code snippet that doesn’t use C.R.U.D. functions from v3.0). Check out the Custom Order Tables BETA project.