How to split the Wordpress database? FYI - I originally posted this question under the Wordpress Development site here:

https://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database

I have a site that is used to log client and meal information. It is based on Wordpress, but has quite a bit of custom code. We have custom taxonomies set for the client and meal data.

Our problem is that the database has gotten quite large and it's causing the site to operate very slow. I would like to essentially archive out the different years of the database into separate databases so that we could still refer back to some of the data if we needed to, but decrease the size of the database for the active year.

I've searched for an archival tool or plugin that could do this, but haven't been able to find anything (most of the results are for Wordpress archives - not the actual process of archiving out data from the Wordpress database). For example, in my database, the "postmeta" table currently has 2,656,529 rows, the "posts" table has 241,725 rows, and the "term_relationships" table has 1,164,119 rows. The entire size of the database is 356.7 MB.

We have custom reports created for the front-end admins to be able to generate meal &amp; client reports, but they now will break if you select a date range longer than 1 month.

FYI - I've ran the WP Optimize plugin and have removed all post revisions already (which were very few to begin with). What I need to do is actually split these tables of the database up so that all of the meals that have been logged for 2012 are separate from 2013 which neesd to be separate from 2014.

Does anyone have any suggestions as to what we can do to get the database under control?
At almost 3 million rows in the postmeta table and almost 250,000 rows in the posts table, it sounds like you have long-ago outgrown WordPress.

The custom post types and custom taxonomies in WordPress were developed as an after-thought so the implementation won't be that efficient. (A lot of WordPress isn't very efficient, but that's another thing altogether.)

Rather than building a "hack" to work around the WordPress "hack" that is custom post types and custom taxonomies, I would suggest moving the client and meal data out of the posts and postmeta table and moving them into a custom table(s). You can still use WordPress as the CMS for the site (and even use their database classes and helpers), but you'll gain a lot of efficiency by creating your own tables and PHP code to manage this information instead of trying to make it work in tables that weren't designed to hold that data...

One example that comes to mind is the GigPress plugin, which creates its own tables to store event/artist/venue information, not the posts and postmeta table.

The biggest problem I see with trying to modify or partition the native WordPress tables is that whenever WordPress is updated, it could/would break your changes. You don't want to put yourself in a position where you have to spend lots of time just to update to a newer version of WordPress.