New to the community? Start here

multi-db: Joins between global tables and blog tables

I did some searching around on the forum and didn’t find a thread that speaks to this issue (but I might have missed it, so apologies in advance).

I am assuming that doing a join between a global table and a blog table is not kosher. Correct?

Or, is it my lucky day, and the multi-db class does some magic to make this possible?

Here’s a simple example of the type of thing I know exists in some of the code…

SELECT * FROM $table_blog_posts, $table_blogs

WHERE $table_blogs.public = 1 AND $table_blogs.deleted = 0 AND $table_blogs.spam = 0;

Now clearly this code can be rewritten to confirm the publicness and the non-spamness and non-deletedness of the blog before it attempts to do the select… but I see this sort of thing in plugins around… Hell, I admit, I wrote some code that does this.

So, if I am correct that this code needs to be rewritten… then… does anyone have any strategies on hunting down this kind of code.

Thanks in advance.

Thu, 22 May 2008 19:47:07
  • Andrew
    • Champion of Loops
      I have to assume that there’s not an issue in the wpmu core, since you don’t mention it as an issue.

    Yeh, joins across databases is a known issue and there shouldn’t be any in the core code. They’d be stuck up the same tree over in wp.com land if joins were introduced that involved global database and blog tables.

    Thanks,

    Andrew

  • ZappoMan
    • Design Lord, Child of Thor

    Ok… I get it.

    Not that I’m suggesting the following is a good idea or anything… but… it is the case that MySQL does support joins across databases within the same instance/daemon.

    So for example the following SQL would work….

    select g.blog_id,b.* from wpmu_global.wp_blogs g, wpmu_a.wp_99_posts b where g.blog_id = 99;

    Now, I can see why you wouldn’t want to do this… since certainly, it won’t scale once you move to different mysql instances and different server machines running those instances.

    So for that reason alone, I think it makes sense to bite the bullet and hunt down any code like this and kill it.

    Thanks for you insight.

  • ZappoMan
    • Design Lord, Child of Thor

    My goal is to move to multi-db… that simple… I am sure that some plugins out there do the thing that we just discussed as being a “no no”.

    I have to assume that other people have had to think this through and handle it….

    My current plan is to brute force it… read through all the code, make sure no plugin attempts to do this.