How to split the a table in database?

Question

Is there any option to split the tables in more tables in different databases? The problem is that my the wp_posts table is to big and it takes a lot of time to show in the Dashboards... (more than 20 seconds). I tried the HyperDB but this plugin just can split the database to different databases but the tables remain the same... Example: wp_posts from database1 and wp_postmeta from database2, but I need to read and to write other ways...

Something like below

database1 --> wp_posts [from row 0--10000]

database2 --> wp_posts [from row 10001 to 20000]

etc...

I think that searching a single post between a lot of databases is not a big problem... there will be a great effect when searching through a hundred thousand of posts a specific query... As my project idea is to make WordPress to handle a lot of products (posts), so analyzing a single table (wp_posts) takes a lot of time, but splitting this table to more tables in different databases (a databases will have it's own server) will show a very impressive result.

Is this possible or not?

Answer 1

Sharding is probably not what you want, especially if the admin area is the only problem. If things are working fine on the front end, you're probably okay.

How many posts are we talking about total?

Some things worth trying (please back up your DB first!):

1. Remove all old post revisions

A quick count of post revisions in my site reveals 5080 of them -- there are only a total of 700 posts.

Running a delete query like this should do it. Please test first! Do not just run it on your production database.

DELETE FROM {yourprefix}_posts WHERE post_type = 'revision';

2. Run an Optimize Table Command

OPTIMIZE TABLE is meant to clean up tables that change frequently. Since you just deleted what was probably a large number of rows, it can't hurt.

OPTIMIZE TABLE {yourprefix}_posts;

3. Stop WP from Saving So Many Post Revisions

There is a constant you can define in wp-config.php to do this.

You can disable them:

<?php
define('WP_POST_REVISIONS', false);

Or limit them a max number.

<?php
define('WP_POST_REVISIONS', 3);

4. Remove Old/Spam Comments

You could also try removing old spam comments from the comments table as well:

DELETE FROM {yourprefix}_comments WHERE comment_approved != '1';