June 2015: Never-Never Land: The WordPress Database

Database expert Sonja London presented on the WordPress Database in June 2015. She summarized her talk as follows:

For many, the WP database is thing that gets created when we set up WP and then forgotten until it fails.  This program will try to empower you to leverage the database to create and maintain a more powerful, efficient WP site.  

We will briefly cover a few things you should be doing to keep it working properly – backup, import, export, cleanup and optimization.  We will take a quick look at its structure.  

Then we will look at some ideas for enhancements and show a few demos, including one with 50k users and another that draws pretty pictures directly from the database.  Finally, we will look at a few tools to help you connect with the data.

Sallie’s Notes

Back up your database–to multiple locations. There are lots of plugins that will do this for you automatically.

Maintain your database: clean out the overhead. Revisions pile up. Also note that revisions don’t save post meta, only post content.

Side note: you can have your staging and production in the same database, with different prefixes.

MySQL Storage Engines

There are more of these than you probably imagined. WordPress now defaults to InnoDB but your host may override this. WordPress used to use MyISAM, which works fine if you are just reading and writing to the database. It’s not so good for things like financial transactions, however.

Don’t know which engine you’re using? Type ‘SHOW ENGINES;‘ into your SQL command field. That shows you the engines available to you, and tells you which one is the default. There’s an engine called Memory which is fast as hell but not reliable. Good for temporary caching.

InnoDB is the engine you need if you are doing transactions: it won’t go into the database until all parts of it succeed.

InnoDB puts a heavier load on the machine. But it also does row-level locking rather than table-level locking. Doesn’t rely on operating system to store files.

Stick to lowercase for your table names; mixed case can result in duplicate tables if you move from Windows to Linux.

Writing Code for the Database

WordPress provides an abstraction layer via wp_query and wp_user_query. When you run a query, it brings everything into memory, so consider only querying a portion of the DB at a time.

Sanitize your input! Make sure to esc_sql($sql).

Sonja explains how SQL injections work and why you should remove the user ID 1.

Most of WP’s higher-level functions are already sanitized, BUT if you query the database directly, you need to do the sanitizing. Pass your statement through PREPARE.

The most important relationship in the database: posts to post_meta, users to user_meta

Avoid problems with prefixes by using $wpdb->tablename in your MySQL queries

Use the WP query functions IF YOU CAN…but there are some times when they don’t do what you need.

Sonja demonstrates some custom tables for propane pricing and how she uses the Google Visualizer to create a graph.

EXPLAIN statement provides information about how MySQL executes statements.

People think correlated subqueries will be slow, but in fact the optimizer deals with it.

Take time to test. Create test data and test users.

Plugins for the WP Database

DB-Table-Editor, which is definitely a plugin for developers. You will have to write code. But you can add and modify rows. (You have to create the tables in phpMyAdmin.)

WordPress Visualizer, which uses the Google Visualization API to graph data from your DB. You need to create a PHP array in the correct format. Sonja says that the work she did for the SPUG site took abut 300 lines of code, including creating the shortcodes.

Contact Form DB allows form plugins to store submissions in the WP database. (Gravity Forms does this already, but Contact Form 7 and many others do not.)

Query Monitor shows you all the database queries performed on the current page.

Creating indexes in MySQL

Sonja likes MySQL Workbench. Anca runs Sequel Pro on her computer to play with queries.

Anca asks about optimizing via changing indexes–Sonja mentions creating a new index for user_meta values in order to verify membership by something other than username. Queries were seriously slow (10 minutes!), so Sonja created a new index to speed it up.

Serialized data–it’s a pain, but we have to deal with it. Searching that is…more than a little difficult. Sonja hasn’t found a really good solution.

Adam asks about creating a new table (not one of the standard 11 that WP uses). He’d like to create new tables rather than using post_meta. CREATE TABLE, give it an ID, auto increment.

The Codex is your friend.

Anca has used a plugin called Stream, which used to be great and is not as nice since they’ve tried to move it to SaaS. Note that the cloud storage isn’t PCI or HIPAA compliant. Be very careful about storing data.

Hushmail: HIPAA compliant email.