Split WP_POST table based on post types

2 comments

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.

2. 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.com.

   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.

Comments are closed.