MySQL Forums
Forum List  »  Optimizer & Parser

1000 Columns, 1000 Tables, or what?
Posted by: Fernando Oliveira
Date: April 19, 2012 08:09AM


Firstly, I'm new here. Nice to be part of these Forums. Firstly, there are so many forums I'm a bit lost, so to clarify, I'm posting here because what I need is a way to make this work without breaking my server limits.

I'm thinking of doing that by optimizing the Database itself, but if optimizing the queries would work and really handle everything, really well, I'd be the happiest person ever. So I think optimization is the best Forum for me. If I'm wrong, sorry, and please tell me where should I be.

Now to explain the problem:

I'm having quite an issue. We tried a project for a client using wordpress multisites, but the plan is to have more than a thousand blogs. Now, at about 600, WordPress started to break.

So looking at the DB, I noticed it creates 9 tables for every blog, creating a new, independent instance of Wordpress. And then writes the post to every single one of those tables.

Allright, that's stupid, I know it, you know it. I'd never write code to do that, it's a time-bomb. Sooner or later the load will be too much (Though there must be a way to make this work, but it was not used).

So I started planning a few changes. I have a nice platform to build over and I have things somewhat planned, so optimization-wise, I was thinking of a single Posts Table, and a way to distribute them to every blog. Or a single posts table PLUS a way to get values from the main blog table, but I don't think that would work that well.

I thought about one numeric value that would contain either "0" for "All Blogs", "1" for the main blog", or "2" to "n" being the IDs of every other blog. BUt that wouldn't allow me to post to only a few blogs.

So now we get to where I'm at. I see three ways out of this. First is make thousands of Boolean columns, one for every blog, station if the blog receives that post or not.
Second is a table for every blog, probably with two or three columns, specifying only the ID plus a post-reference containing the ID of the post, and maybe a Boolean incase I need to remove it from just this blog, without erasing the value from the table completely.
Third is to make a table for every blog and find a way to write everything without the PHP breaking it because of memory limit etc., and which will last under heavy loads. I imagine I should make the queries on MySQL and call only one querie from the PHP, then MySQL would do the job, and I imagine it wouldn't break as easily.

Now please, if anyone with more experience can enlighten me on this matter, it would be really nice, since I'm pretty f***ed on this one.

Thanks in advance, and sorry again if I missed the forum's purpose.

Options: ReplyQuote

Written By
1000 Columns, 1000 Tables, or what?
April 19, 2012 08:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.