database - Split WP_POST table based on post types

admin管理员组

文章数量:1279051

I was wondering if it's possible to get my posts from 2 databases depending on the post type.

I'm using HyperDB but can't seem to get it to specify the database based on post type. I Basically need all my "product" post types getting pulled form another DB to the general WP posts (media, pages etc) ?

Any thoughts much appreciated

I was wondering if it's possible to get my posts from 2 databases depending on the post type.

I'm using HyperDB but can't seem to get it to specify the database based on post type. I Basically need all my "product" post types getting pulled form another DB to the general WP posts (media, pages etc) ?

Any thoughts much appreciated

• posts
• database
• hyperdb

Share Improve this question asked May 16, 2013 at 8:11 user31956user31956 1133 bronze badges 1

• There's now a plugin that might do this, see stackoverflow/questions/45662425/… – swissspidy Commented Jan 19, 2018 at 13:14

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You could filter 'query' (you get the complete SQL here) and search for the post type in that. Then switch the database depending on what you found.

But I don’t think this would work well:

• You might end up with the same post ID used multiple times (in each DB) and some strange side effects (comments, taxonomies).
• Some queries include multiple post types; you would have to rewrite the query to search in all tables.
• Some queries ask just for a post status or a date … again, you have to go to both databases.

Not a great idea in my opinion.

You could, of course, resort to some voodoo, filter the query or do some magic in HyperDB or MySQL Proxy, etc., but that's just going to be so fragile. You haven't specified why you want to query products from another database, so I'm going to assume you just want to shard it for scalability.

When doing any kind of sharding with MySQL, you have to first identify the smallest possible unit of data, that can be self-contained on a single shard. Unfortunately for WordPress, that unit is the entire collection of WordPress site tables, minus the global tables (wp_users + wp_usermeta) and Multisite tables (wp_blogs, wp_site, wp_sitemeta, etc.)

So you can separate the users from the site. You can separate one site from another site, but you can't separate the comments from posts, or taxonomies from terms. In other words, sharding is great for multisite environments, like WordPress.

Sure, you can ask HyperDB to look for a wp_comments table in your query and send it to another server, but then querying a comment will no longer work, because it will attempt to JOIN it with the posts table, which is nowhere to be found.

At this point you might think: "Aha! I'll just split the query, fetch all the comments from one database first, get their post IDs, then send those IDs to another database, and perform the join in PHP!"

Well, something similar has already been attempted with WP_Query's split_the_query for a variety of other reasons, mostly related to object caching. It's a very simple approach to potentially query the same data (posts) from two different places (db, redis/memcached) and combine the results. And yet it's so complex that many seasoned developers don't understand how it works or why. Now image somebody tossed a JOIN there :)

Then, in theory, if we did manage to somehow query two databases and (inner, left, right) join the results in PHP, the cost of doing that will very likely be orders of magnitudes slower, compared to having MySQL perform the join and just send us the results. So we still lose in the performance test.

The best solution would be to have the e-commerce plugin store products in the products table. Then the sharding unit could be the products table plus any other tables it can potentially JOIN on. If one can guarantee that set of tables, then it would be safe to move them to another database, and set the appropriate dataset in HyperDB.

If you're working with WooCommerce, there's a Custom Product Tables Beta feature plugin that's being worked on by the Woo core team, which is an attempt to remediate the bad decision (hindsight 20/20) of doing CPT. If you're using anything outside of WooCommerce core + Storefront though, you'll likely need to make some adjustments for everything to work.

Hope that helps.

本文标签： databaseSplit WPPOST table based on post types