The MU forums have moved to WordPress.org

Database Tables per Blog (19 posts)

  1. Allstar
    Member
    Posted 17 years ago #

    I will admit I'm not the wisest web head in the world but this I don't understand:

    Why is it that comments, categories, tags and meta etc. are connected to their post with a POST_ID but the same thing hasn't been done with a post and their blog via a BLOG_ID in Wordpress MU?

  2. quenting
    Member
    Posted 17 years ago #

    there's one post table per blog. So there's no need for the blog id, because there's only those blog's post in that table. on the other hand for a given blog, each post has several comments, each category several posts, hence the need for post_id

  3. Allstar
    Member
    Posted 17 years ago #

    Thanks for the response.

    Sorry, I guess I must've explained what I meant ambiguously, in the Wordpress Mu database scheme if you had a 'post2blog' database table then all posts for all blogs would be under the 'posts' table and then you wouldn't need wp_1_posts, wp_2_posts for the wp_1, wp_2, blogs. This would reduce the number of tables dramatically and less tables is surely easier to manage?

    Obviously this could be applied to some of the other 'wp_N_' tables aswell.

  4. lunabyte
    Member
    Posted 17 years ago #

    The thing is, let's say you have 500 user blogs. Each of those have an average of ~300 posts and pages per blog.

    That's some 150,000 posts/pages total. Funny as it sounds, it's faster for mysql to look up something in 1 of 500 tables with ~300 entries, than it is for it to sort through 150,000 rows in a single table. At least according to what I read in the mysql manual yesterday in regards to optimization.

    Now, also to consider is scaling purposes. If you get to a point where you need to break up the user tables into multiple db's, having a table like you're describing would hinder that process.

    So yes, less tables are easier to manage from an admin side since there is less to keep track of, but at the same time, it's more abusive on the server as well.

    It's been a topic of a lot of debate here, and you can see what's come of it. (The current db structure).

    The question that "should" be in circulation, is whether a single global db with the global db tables, and then putting each user in their own db would be more efficient. Of course, then you're managing connections to 100's or 1000's of db's too. So it comes down to working with a ton of data, and what's better.

    If each user had their own db for their tables, that would be easier to manage, and easier to scale than separate tables. Then separate tables are easier to scale that single global tables, but the single global tables are easier to manage then 1000's of tables.

    A vicious cycle indeed.

  5. quenting
    Member
    Posted 17 years ago #

    allstar, look here:
    http://mu.wordpress.org/forums/tags.php?tag=database
    you'll find this has been indeed debated quite a bit. There are pros and cons for each structure, but in a nutshell:
    - separating tables is better for sticking the MU code to the base WP (-> faster development cycle) and for sizing (makes things simpler if you want to spread your DB on multiple machines).
    - grouping them is better for implementing sitewide features, and general database management, updates etc.

  6. Allstar
    Member
    Posted 17 years ago #

    Thank you so very much for the astute answers. I don't suppose there will ever be a choice which implementation you wish to use when installing? lol.

    Well whilst Wordpress is ( in my opinion ) the best solution to my blogging needs I think my hosting limitations which are big enough for a sizable community aren't big enough to cope with 8 tables/person * 400 active members which is what I expect to use over the next year.

    I've browsed around the pages and some searches and seen there are methods to reducing the load but I would like advice asto what sort load I'm going to be dropping on my hosts? Currently I've got a phpBB with 200 active members and 25,000 posts at ~50 post/day and according to my host we've never exceeded using around 20% of their average.

    I don't want to have to get involved in this mutliple dedicated servers malarky, this is a charity thing and I don't have the money!

  7. quenting
    Member
    Posted 17 years ago #

    > I don't suppose there will ever be a choice which implementation you wish to use when installing?

    Well you can choose to install lyceum, which is a single-table version of MU. *But* (there is a but), it's unofficial, and due to the much more different structure from the base WP (see point 1 above ) it is much longer to get features (and most importantly plugins) out of WP into lyceum than out of WP into MU.

    400 active users isn't that big a number as far as the number of tables is concerned. Means 3200 tables, which unfortunately is above the phpmyadmin-manageable size, but which a not-too-big server should be able to handle allright.

    To give you an idea, in my set up I have currently 50000 blogs on 2 servers (and just started renting the 3rd, which means those two are close to full). Mind you, plenty of them are innactive, splogs, or duplicate blogs that were never used, and only around 10000 users have more than 5 posts. That gives you an idea though, it means around 1500-2500 online visitors in forum terms (action in the last 15 minutes) if you want to compare with phpbb for instance. My servers are core2duo 6600 (2*2400GHz) with 2 gigs of ram each, which cost around 170 euros a month. With such a server you can probably easily handle your phpbb forum and your 400 MU-powered blogs.

    Just my 2c.

  8. Allstar
    Member
    Posted 17 years ago #

    £114 per month!?! I'm never going to have that much available to spend!

    The comparison to number of visitors is very useful, I will be going ahead with this on a limited basis to start with, possibly just the mods/staff then see how well the server, my package and I get along with it.

    I'll let you know how I get on, cheers.

  9. psenthilraja
    Member
    Posted 16 years ago #

    Hi,

    I have a scenario, where i have to provide search feature across all blogs to the user.
    Since the MU blogs are installed inside the intranet, we could not use google search to search across blogs in our site.

    Also, we plan to include the following features.
    Search across selected blogs like "Favorite blogs" etc.

    Search across favorite posts.. we have included a feature where a user can mark a particular post to his favorite list.

    Next, we need to display recent posts & comments across all blogs.

    And many similar functionalities that requires operation across blogs..

    I feel, highly restrained, particularly in implementing the search criteria..

    Any help on these?

  10. andrea_r
    Moderator
    Posted 16 years ago #

  11. mzvarik
    Member
    Posted 15 years ago #

    So, is it better (performance):

    to have for each blog ONE database
    OR
    to have lots of blog tables - WPMU default?

    I appreciate your sum up comments,
    Martin

  12. MindStalker
    Member
    Posted 15 years ago #

    mzvarik: It depends upon your need and usage. If each blog is a completely separate entity it makes sense to give each blog its own tables, in fact I'd give each blog its own database (database as in Create Database; Use database not entire server etc) that way you don't have potential name collisions.
    If your blogs are mixed together, especially if you want to provide search facilities across all blogs you'd want to use a single table. Searching thousands of small tables can take a lot more time and programming than searching one large table.

  13. goodevilgenius
    Member
    Posted 15 years ago #

    I was just thinking about this, because I think it would be great if it were just as easy to search all the blogs as searching one. A multi-blog WP_Query would be great.

    So, I thought, what if you created a view of the posts tables as one table? This would only work for MySQL 5+, but this could be an option.

    So, to test it out, I manually created a View in my database that I think could work. Here's the SQL command:
    CREATE VIEW wp_all_posts AS SELECT *, 1 as blog_id FROM wp_1_posts UNION SELECT *, 2 as blog_id FROM wp_2_posts ...

    Obviously, this would need to be updated everytime a blog is created/deleted, and I'm no MySQL expert, so I don't know how feasible this would be for several hundred blogs. Obviously the sql command would be huge, so maybe that's prohibitive.

    Of course, all I have is a view. I have no way to use query_posts() to get at it. I'm not sure how I would go about it. And I haven't looked into the code to see what I would need to do about modifying the view for each new blog. I really just had half an idea, and thought I'd throw it out to see if gives anyone else any other ideas.

  14. andrea_r
    Moderator
    Posted 15 years ago #

    "I think it would be great if it were just as easy to search all the blogs as searching one."

    there's alreayd plugins for that.

    And yes, your VIEW up there, to get results as written, would probably bring a large setup right to a crawl.

  15. goodevilgenius
    Member
    Posted 15 years ago #

    "there's already plugins for that."

    I don't mean specifically just searching, but any query. It'd be nice if there were a way for the query_posts() function to be able to query all blogs, rather than just the current. That's really what I'm getting at.

    "And yes, your VIEW up there, to get results as written, would probably bring a large setup right to a crawl."

    Oh well. I figured as much, but thought I'd throw it out there to see what other thought. Now I know.

  16. goodevilgenius
    Member
    Posted 15 years ago #

    I guess I should have looked through wpmu-functions.php.
    I figured out how easy it is to do exactly what I wanted using get_blog_list() or get_blogs_of_user(), along with switch_to_blog(). Not hard at all!

  17. andrea_r
    Moderator
    Posted 15 years ago #

    Yep. :) Funny what you find when you read the code. :D

    There's also this:
    http://premium.wpmudev.org/project/post-indexer

  18. vanarie
    Member
    Posted 14 years ago #

    I was disappointed to learn that with MU it creates new tables for each blog. I understand the speed issue in terms of having to sort through one huge table vs. the one for the specific blog you're looking at. Though, that's what indexing is for in the first place. But indeed it would be faster.

    From an admin point of view, having separate tables is a big negative when it comes to doing any mass updating or managing it with phpMyAdmin. I would probably use MU if it weren't for this "feature". It would be nice to have the option to turn it off and use one table!

    My 2 cents.

  19. cafespain
    Member
    Posted 14 years ago #

    @vanarie: from an admin point of view having one huge table is a bigger negative. Because once you get above a certain size, scaling and sharding becomes an issue.

    Their are trade offs for both approaches, but personally I, and I'm sure a lot of others on here who are running large sites, prefer the multi-tables approach.

About this Topic

  • Started 17 years ago by Allstar
  • Latest reply from cafespain