Jan 26, 2009
Where Is blog_id In Wordpress? Respect MySQL!
There are some blogs where one has a few posts and few comments (if any). Let's say you have 25 of them in one database. And each blog has about 13 tables by default. So in total ... there's a whooping 325 tables. OMFG?! This is indecent even if, maybe MySQL is cool with it.
This could have been fixed by adding a new field blog_id in all the tables and a new table called $table_prefix.blogs. And each table record should be associated with a blog_id to a blog. And, in the end, it would be the users choice to change or keep the table prefix. And if you set a new table prefix then just one blog will be set on those tables but if multiple blogs shared the same prefix then each would have a new blog_id.
The blog_ids could be generated based on blog base URL subdomain (which I think is best - folder installs for WP are not best choices). And the 325 tables for 25 blogs with up to 25 posts each would be spared and condensed into the original 13. MySQL decency rules!
A fix for this!
I have no solution for now. I noticed a filter called query but not all SQL calls go through it as plugins are loaded after at least one SQL query is made. I'm trying to find a non-invasive solution but there's none so, right now, I'm trying to see what can be done to make this with least Wordpress code alterations for future smooth upgrades.
What do you think?
PS: If this is possible let me know :) Don't tell me you can share a userbase between installs. I want posts in same table for multiple blogs.
Hey, MA.TT ... think about this or point the obvious out for me.
As I understand it, this has already been discussed dozens of times among WP developers, and you’re extremely unlikely to get your wish. This thread has links to some relevant discussion.
Good read. So all think a lot of records will impact performance.
But why not let me make that choice based on the table_prefix? I know how many posts each blog will have.
PS: Eventually I’ll have to release a Blogging Engine built from the developers point of view.
Like elHttpClient: FPBP (for pros by pros)! :)
I don’t think you’ll get any performance gain by going through all the work to get all the blogs onto a handful of tables and the only other reason I can think to want to change is to make DB management a little easier which is mostly an automated task for me.
I guess what I’m saying is what’s the point? To make your DB look pretty?
I want to end up building a switch function to allow me to control any blog from the same control panel.
The DB looks better and the queries on INFORMATION_SCHEMA are faster.
I’m a little confused as to what you are trying to accomplish. Less tables is…good…?
Are you trying to setup multiple blogs and administer from the same control panel? Have you tried WordPress MU?
Doesn’t really solve the tables problem…
Communibus
Less tables is good if each blog would have a few posts / comments. Easier to manage, even using a raw MySQL manager which I often do.
Wordpress MU is still at version 2.6.X and also has the many tables I wanted to get rid of.
Last time I used WordPress MU it was at 1.3 so I’ll take 2.6.X but I did read recently that it’s not that far behind WordPress 2.7.
Regardless, I think you are talking major plumbing work on the MySQL side, no?
Maybe another blogging platform? I can’t recommend any because I just use WordPress but maybe that or something home grown might be the answer. I have had the sneaky suspicion that Googlebot is smart enough now to parse and “understand” WordPress blogs now so maybe a custom job wouldn’t be a bad idea for SEO?
Communibus
The great thing about WP is the control panel.
This would take a bit to rewrite and I ain’t got the time right now.
But in the near future … I’m thinking of writing a new blogging platform.
I agree, it makes a lot more sense to use blog_id. Lyceum does what you’re talking about. Unfortunately, WP plugins are less likely to work with Lyceum than with WPMU.