Splitting posts+pages and attachments into separate tables

  1. David Torbert

    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).

    Posted: 8 years ago #
  2. Andy Macaulay-Brook


    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.

    Posted: 8 years ago #
  3. ctrlaltdelete


    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.

    Posted: 1 year ago #
  4. 10 CPT with 1000 posts is 10,000 posts per site.

    Let's assume you have 5 Multisites on that old network.

    50,000 posts.

    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

    Posted: 1 year ago #
  5. rjrobinson


    I would very much support this. In many sites, updates go in two quite distinct cycles: an ongoing series of blog posts, published as and when they are written; and regular phased releases (monthly, quarterly) of page-based content. This is hard to do in a disciplined manner if pages and posts are handled by the same table. Separating posts and pages into different tables would make this much easier.

    Posted: 1 year ago #
  6. Vishnu

    Hi Ipstenu

    Our Website is Youtube Based Site where youtube Submit URL and Video is Published.

    All images and Post come in wp_post and we always face data connection issue .

    How can we handle such big db having 200K post every month

    Posted: 1 year ago #
  7. greplay

    I happened this problem too & my website used bbpress,bbpress's topic&reply alse inert into this table by data is increating i cant imagine what will happen, waiting the best answer!God help!

    Posted: 11 months ago #
  8. You hire a sysadmin to help you scale your server. If you're adding 200k posts a month, you need one because there's no magic answer here. You've entered high-level stuff.

    Posted: 11 months ago #

RSS feed for this topic


You must log in to post.

  • Rating

    25 Votes
  • Status

    Sorry, not right now