Splitting posts+pages and attachments into separate tables
Right now, every post, page, or attachment is stored in the wp_posts table. This means that if you have 10,000 images and 50 posts+pages, every time you go to view a post or a page, it's querying a 10,000-row table instead of a 50-row table.
The table could be partitioned into wp_posts and wp_attachments, then if a query needed to be run that really did require searching both, you would use a view instead.
Written correctly, the schema could be written to allow other kinds of logical partitioning (eg, partitioning the tables by year).
Philosophically I think this is going the opposite way to WordPress. With custom post types, it sort of makes sense to bring more into the same table rather than split it. If you're in the unusual position of having so many images, and if you have a performance problem because of it, then maybe a plugin that uses its own db tables is the answer.
It's a train wreck actually.
I come from a multisite with 10 CPT each with 500-1000 posts.
When i merged all those into a single WP installation, mysql went bananas. It's just too much stuff for 1 table.
I need a $150 dedicated server now to be able to handle the load which was easily handled by a $20 VPS before the merge.
I wish this would be discussed more.
Separating each CPT on it's own table would totally fix any bottleneck.
10 CPT with 1000 posts is 10,000 posts per site.
Let's assume you have 5 Multisites on that old network.
Yeah, that's a lot of posts. But it's not really the volume of posts so much as the queries you're running.
Assuming the basics:
* Database is optimized
* Revisions are limited
* Object caching is enabled
You'd want to look into solutions like http://wordpress.stackexchange.com/questions/166448/how-to-optimize-wp-site-for-millions-of-posts
RSS feed for this topic
You must log in to post.