Partitioning table post into several databases

I was searching for this and it seems it is impossible

It is possible, it’s hard but you can do it, hyperdb configs database partitioning, this doesn’t partition an exists db, this recognices more than one db to use with WordPress.

Can you please describe or show how it is possible points 2 and 3? :
2. database “B” will have tables for posts up to ID 1000
3. database “C” will have tables for posts form ID 1000 to 2000

@adlbeat are you sure it is possible to split wp_posts table into multiple databases? Could you give more details?

By the way, why this topic is marked as resolved?

Alexandru Vornicescu & Rodrigo Primo:

Yeah it’s possible, look, you only need to edit db-config.php
the db.php class shouldn’t be modified. Is marked as solved because i solved it days before.

I will use spanish because i’m mexican and i wrote it previously in spanish (now i’m usin copy-paste), use a traslator if required.

I made it while doing scholar practices at a newspaper factory, they use WordPress and were required to split databse because there were a huge DB (4GB DB size)
This is an example from a distributed database wich has 3 DB, but you can do more.
1DB for all wp-tables except posts
2DB just for posts. (

2. database “B” will have tables for posts up to ID 1000
3. database “C” will have tables for posts form ID 1000 to 2000

)

This is just an example, adapt it to yur case (sorry my bad english)

/**
* Considere que ‘wp-develop’ es la BD maestra, la BD ‘wp-develop1’ y ‘wp-develop2’
* contienen los posts pero en fragmentos.
*/

/**
* Base de datos principal (no posts)
*/

$wpdb->add_database(array(
	'host'     => DB_HOST,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => DB_NAME,
	/*'dataset'  => 'global',*/
));

/**
* Base de datos para posts que impide escritura y
* no prioriza lectura (posts de a?os o semestres anteriores)
* Es posible partir ésta BD aun en más.
*/

$wpdb->add_database(array(
	'host'     => DB_HOST,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => 'wp-develop2',
	'write'	   => 0,
	'read'	   => 3,
	'dataset'  => 'news',
));

/**
* Base de datos para posts a escribir/leer, máxima prioridad
* en escritura/lectura (a?o actual o semestre actual)
*/

$wpdb->add_database(array(
	'host'     => DB_HOST,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => 'wp-develop3',
	'dataset'  => 'news',
));

/**
* Reconoce tablas “separadas” de la B.D. y las integra
* En ‘News’ dataset, donde news son los posts.
*/

$wpdb->add_table('news', 'wp_posts');
$wpdb->add_table('news', 'wp_postmeta');

If u have questions feel free to posts them, i’ll help you. Greetings

@adlbeat did you then split up the database between different servers or still keep the split up parts on one server? also did you see performance improvements?

I’m having the same exact problem on a Non-Multisite website where the DB is about 4 gigs now with millions of posts and its getting very slow.

This code does not split the posts to different tables, it will only store to one database named ‘wp-develop3’

Look:
We have created 3 databases,
1st database will have all tables except wp_posts and wp_postmeta

2nd database will have nothing because we set him only to read and not to write

3rd database will have wp_posts and wp_postmeta tables

So where is the spliting of table wp_posts to different tables???

If we will set 2nd database to write then this will be switching between databases.

So this seems impossible until someone will show me how this is possible!

I’m also under the impression that this code does not split the posts to different tables.

zerackam:
In this case I split the DB in the same server, but you can distribute it on diferent ones, if you chose to do the second options y think is perfect, but splittin’ in the same server improve performance, WP uses foreach everywhere and it’s slow when the querys are ran on big tables (or BD’s).

Alexandru Vornicescu:
You are allright, this does not split the database, but recognices a WordPress Distributed databases, in fact u need to split the database, then implement the php class and join all databases.

We have 3 dtabases:

1st database will have all tables except wp_posts and wp_postmeta

(ok)

2nd database will have nothing because we set him only to read and not to write

(wrong) -> In this table we have all the new posts stuff, here are the new and fresh post content so this DB is fast.

3rd database will have wp_posts and wp_postmeta tables

(OK)
In here we have oldests post, the old stuff but like this DB you can do more than one, i just used one, but if you need you can work with more DB like this. Here is the performance trick.

The split is in DB 2 and DB3:
In DB2 we can have this year’s stuff.
DB3 can be used to store the oldest content (But we can do more DB’s like this, one per year or month, etc.).

The C.R.U.D. in DataBase is distributed in the system so there’s no switching beetwen dsatabases

Rodrigo Primo:
You’re right, this just “joins” the distributed databases, The spliting can be made easily typing some querys using the prompt (Eg. MySQL Shell)

If you are looking for an automatization of this (Eg. if you want a db for every year auto-generated) you can use cronjobs or write a script wich ask to server the actual date, then runs the script.

Greeting.