Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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 & 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?

share|improve this question
    
350 megs isn't too bad, what does your host say? You could try moving the databases to solid state drives, adding more memory/processors on the server or otherwise throwing more power at it. –  Andrew Bartel May 28 at 22:13
1  
I host the site locally on my own dedicated server. I know the size itself isn't necessarily the problem, but I would figure the 2.6 million rows under postmeta are definitely causing it to run slow. I really just need to know of a good way to be able to split this up at least for testing purposes. –  Justin May 28 at 22:22
    
In that case use hyper db –  Andrew Bartel May 29 at 2:55
    
Have you used this Wordpress plugin Andrew? I looked through the plugin details and FAQ, but I don't see how to actually split tables. It states that the plugin can partition off certain tables to be in different databases. It doesn't state that it can split those tables based on a date range. –  Justin May 29 at 14:32
    
I have not and I'd have to look at the database myself to really give you much more help, but, I'd probably post this question on dba stack exchange as it's not really WordPress specific and is about managing a database with a lot of rows in one specific table. –  Andrew Bartel May 29 at 15:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.