About me: Mohammad (Mo) Jangda

[email protected] | [email protected] | @mjangda

– Toronto, ON
– Code Wrangler at Automattic / WordPress.com
– Ice Cream Fan

Outline

– API vs Database
– Core Structure & Table Walkthrough
– Points of Interest
– Pitfalls & Opportunities

Caveats

– Not really touching much on Multisite
– High-level concepts
– Ignoring links
– Assuming MySQL only
– No database tuning advice :)

A Very Rich API

As devs, we don’t have to worry about schema because WordPress abstracts the database interaction for us.

The closest thing most devs need to worry about is the username and password during the install process.

Why bother?

– Because it’s interesting :)
– Important to understand the underlying architecture of the system
– Easier to debug problems and understand changes and new features
– Can help solve interesting problems

Core Structure

– Single-site: 11 base tables
– Multisite: 17 base tables

=== 9 additional tables for every new blog
=== 500 million tables on WordPress.com

Core Structure

– Hybrid entity/object-oriented and key-value store (posts + meta)
– Some normalization of the schema (terms)
– Unique IDs (primary key) for each entity within a table

wp_{object}s tables

Main tables are modelled after the object they are storing:
– wp_posts
– wp_comments
– wp_users

wp_{object}meta tables

Each object type has a key-value meta store:
– wp_postmeta
– wp_usermeta
– wp_commentmeta

Consistency across these tables allows for a common metadata API

key-value store

A bit like an associative array, in table form. Values relating to objects are identified by a key.

(“NoSQL”)

key-value store

– post_id: 123
– key: _thumbnail_id
– value: 456

key can any valid varchar(255); value can be any primitive or serializable object.

wp_term(s|taxonomy|taxonomy_relationships)

– Terms are handled very differently via three tables
– Somewhat messy and the source of many frustrations

Table: options

Similar key-value store to meta but only one object (blog) and no matching table for that object

Table: options

option_id [bigint(20)]

option_name [varchar(64)]

option_value [longtext]

autoload [varchar(20)]

SELECT * FROM wp_options WHERE autoload = ‘yes’

// get_option( ‘cookies’ )
SELECT * FROM wp_options WHERE option_name = ‘cookies’

Table: posts

http://codex.wordpress.org/Database_Description#Table:_wp_posts

Table: postmeta

http://codex.wordpress.org/Database_Description#Table:_wp_postmeta

Table: comments

http://codex.wordpress.org/Database_Description#Table:_wp_comments

Table: commentmeta

Look familiar? :)

http://codex.wordpress.org/Database_Description#Table:_wp_commentmeta

Terms

– Mapping looks something like:

Table: terms

http://codex.wordpress.org/Database_Description#Table:_wp_terms

Table: wp_term_taxonomy

http://codex.wordpress.org/Database_Description#Table:_wp_term_taxonomy

Table: wp_term_relationships

http://codex.wordpress.org/Database_Description#Table:_wp_term_taxonomy

#1: Get the term_id from slug

SELECT wp_43654959_term_taxonomy.term_id
FROM wp_43654959_term_taxonomy
INNER JOIN wp_43654959_terms USING (term_id)
WHERE taxonomy = ‘category’
AND wp_43654959_terms.slug IN (‘stuff’)

require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, WP_Tax_Query->get_sql, WP_Tax_Query->clean_query, WP_Tax_Query->transform_query, wpdb->get_col

#2: Get the term_tax_id

SELECT term_taxonomy_id
FROM wp_43654959_term_taxonomy
WHERE taxonomy = ‘category’
AND term_id IN (293)

require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, WP_Tax_Query->get_sql, WP_Tax_Query->clean_query, WP_Tax_Query->transform_query, wpdb->get_col

#3: Get the term object (optional)

SELECT t.*, tt.* FROM wp_43654959_terms AS t INNER JOIN wp_43654959_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = ‘category’ AND t.slug = ‘stuff’ LIMIT 1

require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, get_term_by, wpdb->get_row

#4: Get the posts

SELECT SQL_CALC_FOUND_ROWS wp_43654959_posts.ID FROM wp_43654959_posts INNER JOIN wp_43654959_term_relationships ON (wp_43654959_posts.ID = wp_43654959_term_relationships.object_id) WHERE 1=1 AND ( wp_43654959_term_relationships.term_taxonomy_id IN (1) ) AND wp_43654959_posts.post_type = ‘post’ AND (wp_43654959_posts.post_status = ‘publish’ OR wp_43654959_posts.post_status = ‘private’) GROUP BY wp_43654959_posts.ID ORDER BY wp_43654959_posts.post_date DESC LIMIT 0, 10; SELECT FOUND_ROWS()

require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, wpdb->get_col

Term Frustration: Complicated Queries

Queries usually require one or more JOINs

– get the term_taxonomy_id of the term matching our slug and taxonomy
– get posts which match the term_taxonomy_id via the term_relationships table

Things get even messier when you want to query by multiple terms or taxonomies or a NOT IN

Term Frustrations: #5809-core

Terms with the same slug are bound together. Title or description change impacts the other. Or be forced to use the dreaded `-2` in your slug

https://core.trac.wordpress.org/ticket/5809

(Fixed early November 2014!)

Term Frustrations: No meta!

No key-value/meta store for terms!

Have to rely on hacky workarounds (store encoded/serialized in the description) or plugins (“Taxonomy Meta” or “Meta for Taxonomies”)

Good News!

There are some plans under way to simplify things:

– from 3 tables to 2
– reduce the complexity of queries with back-compat
– pave the way for taxonomy meta and better object modelling

Potential roadmap for taxonomy meta and post relationships

An update on the taxonomy roadmap

add_filter( 'posts_where', function( $where ) {
    // kill the query!
    $where = ' 1 = 0';
    // modify as needed!
    return $where;
} );

global $wpdb
$wpdb->insert( ... )
$wpdb->delete( ... )
$wpdb->get_results( ... )
$wpdb->get_var( ... )

- 	$wp_db_version = 27916; 
+ 	$wp_db_version = 29188; 

– Especially harder on larger multisite installs
– pre_schema_upgrade attempts to handle the upgrade

https://core.trac.wordpress.org/browser/trunk/src/wp-admin/includes/upgrade.php#L2067

– The schema was designed to be extremely flexible
– Not always perfect (e.g. post2post) but can accommodate a huge number of use cases
– But, tools available if your use case requires them (e.g. dbDelta)

Pitfalls: Slow/Complex queries

– WP_Query is very powerful; you can do some insane lookups
– At scale, these insane lookups can break your site
– If you’re not careful, these insane lookups can break the site even with very little traffic

Pitfalls: Slow/Complex queries

– Non-indexed or expensive queries can be a problem
– The structure of the taxonomy system lends itself to slow queries
– meta_key- or meta_value-based queries on a really large tables are slow

Pitfalls: Schemaless Meta

– Some might argue that the flexible schema leads to bad application/site design
– Others also argue that less thought put into how meta will be used

Opportunity: Consistency

– Know what exactly to expect between installs
– Your WordPress is the same as my WordPress

Opportunity: Flexibility

– Custom post types!
– Any manner of object can be stored as custom “posts”
– Keyed off the “post_type” column

Opportunity: Flexibility

– wp_term_relationships does not have a strict definition of what an object_id is
– Used for both posts and links!
– Could technically use for users as well

Opportunity: alt use cases

– Using taxonomy as a post-to-post connector (instead of meta)
– https://github.com/mjangda/taxonomy-to-post_type-sync

Opportunity: alt use cases

– Machine-generated objects as post_types
=== Redirects via WP.com Legacy Redirector
=== DNS records on WordPress.com
=== Sitemaps in Comprehensive Sitemaps

$args = array(
	'post_name' => $from_url_hash,
	'post_title' => $from_url,
	'post_type' => self::POST_TYPE,
	'post_parent' => $redirect_to,
);

wp_insert_post( $args );

Opportunity: alt use cases

– Liveblog entries as comments
=== Completely custom UI for interacting with data
=== All abstracted into its own API (and using the WordPress API) to map data to database fields (WPCOM_Liveblog_Entry, WPCOM_Liveblog_Entry_Query)

https://github.com/Automattic/liveblog

Summary

– The schema provides a good object model + key-value store for data
– It is extremely flexible and powerful
– Not something you should really have to worry about or deal with
– It can present problems if we are not careful
– It can reward us very well if we use it as it was intended to be

Hiring, Hiring, Hiring!

– VIP Wranglers!
– Code Wranglers!
– Designers!
– Happiness Engineers!
– Interns!

http://automattic.com/jobs/