WordPress MU: the database

The merger between WordPress and WordPress MU will be official with the version 3.0 of WordPress, which is scheduled for next April. So I continue to visit WordPress MU, taking in this time a developer point of view. We will discuss the differences between WMPU and the « standard » version of WordPress.

WordPress

I already discussed on the database of WordPress.
Overall, we have

  • table by object:
    • wp_posts contains posts and pages,
    • wp_links contains … links (bookmarks),
    • wp_users stores all users of the blog,
    • wp_comments keeps all comments of posts and / or pages,
    • wp_terms contains tags and categories.
  • tables for the properties of earlier objects
    • wp_postmeta
    • wp_usermeta
    • wp_commentmeta
  • relations tables
    • wp_terms_taxonomy
    • wp_terms_relationship
  • table for « operations »
    • wp_options
Global view of the WordPress database

Wordpres MU

WordPress MU manages a set of blogs. Designers had to choose between:

  • adding a field in each table to indicate the blog id,
  • creating a « set of tables » for each of the blogs.

The first solution is the cleanest. The database schema is build during the creation of the platform, and is not modified after. But this solution has two disadvantages:

  • It is not « scalable ». If the platform has to manage hundreds of very active blogs, tables will become hugues, and the database therefore will require big resources (servers, memory, …) to function properly,
  • Makes it difficult to share code with WordPress standard edition. Indeed, all database queries must be rewritten.

The second solution corrects the previous two drawbacks: but the principle is not elegant, since the base evolves according to the number of blogs created.

Nevertheless, it is the chosen solution. We have therefore, in a database WPMU, a set of tables, duplicated as many times as there are blogs:

  • wp_x_posts,
  • wp_x_postmeta,
  • wp_x_links,
  • wp_x_users,
  • wp_x_commentmeta,
  • wp_x_comments,
  • wp_x_terms,
  • wp_x_usermeta.

With x, the identifier of each blog.

Two tables cannot share the same name. So tables are distinguished by wearing the ID of the blog to which they belong: to the blog whose ID is 2, the tables will be named wp_2_posts, wp_2_links, wp_2_options …

Tables wp_users and wp_usermeta no longer part of such set of tables, since users are common to all blogs.
Similarly, WordPress MU includes tables to share data across blogs:

  • wp_sites manages sites. A site can be likened to a domain. This concept of site in this database is not managed at WPMU level. Even if the features are not yet available, the notion of site already exists in the database. Some plugins HERE or HERE, help us to manage sites,
  • wp_sitemeta contains a number of parameters associated to each site. Among these parameters, many will use as default options for blogs during their creation,
  • wp_sitecategories contains the list of categories of all the blogs a given site,
  • w_registration_log and wp_registration_log: the role of these tables is not very clear to me. I think these tables are used, with the wp_users, to record the activity of users, whether registered or not.

So the overall scheme of this database is as follows:

Global view of the WordPress MU database

Rules Development

To be compatible with both versions of WordPress, and with the future common version 3.0, a plugin or theme should NOT use the prefix wp_ directly. The right way is

  • Either avoid SQL queries using existing functions,
  • Either use the features of class WP.

Example: If we want to collect all posts from a specific custom field, the code could be:

global $wpdb;
$sql = 'SELECT ID, post_title, meta_value FROM wp_posts, wp_postmeta WHERE meta_key="series" and ID=post_id';
$results = $wpdb->get_results($sql);
...

This code doesn’t work with WordPress MU. The correct code is

global $wpdb;
$sql = 'SELECT ID, post_title, meta_value FROM'. $wpdb->posts. ''. $wpdb->postmeta WHERE meta_key="series" and ID=post_id';
$results = $wpdb->get_results ($ sql);
...

In this way the code will work:

  • both with WordPress, with WordPress MU,
  • if the user use a different prefix than the standard one (recommended for security).

Conclusion

The strategic choice to duplicate a set of tables for each blog, provides something pretty heavy from a database point of view. But this structure is ery useful for a developer perspective. In fact, we don’t have to modify any query to move from WordPress to WordPress MU. So, most of plugins or themes can work without modification. The merger between WP and WPMU will be easy also.