When developers think about optimizing their sites, database optimization isn’t always the first thing to come to mind. And yet, a few simple steps and considerations are all you need to make a large difference in performance and user experience.
Why are databases important?
Systems like WordPress use PHP code to generate the pages requested on your site. The PHP code communicates with a database full of options, page IDs, user information and more to help create that page. Often times one of the slowest parts of the page generation process is running queries to your database, and waiting for the results. So any optimizations made to your database can help your page speed, as well as the weight it carries on the server.
In WordPress, the core code has a class called Object Cache, which is meant to store repeated query results and offload some of the work to serve those requests to cache. By default though, the Object Cache class is not persistent, meaning it doesn’t span multiple user sessions. To truly make the most of caching, its best to implement a service like Memcached to allow for persistent Object Caching. Memcached will store the repeated query results in virtual Memory, making the results return lightning fast.
InnoDB vs MyISAM
MyISAM is a legacy type of table storage engine. When a table is formatted in MyISAM, any query run to a row in that table will cause the entire table to lock until the query completes. This is known as “table-level locking.” When multiple queries are run to the table simultaneously, one will inevitably fail because the full table is locked. While MyISAM might work well for databases that only need to support “read” operations, it is not ideal for writes!
Enter InnoDB. InnoDB is a more modern table storage engine format. It’s the best format to use for production databases, providing speed and reliability for both read and write operations. Unlike MyISAM, it allows for row-level locking. And, many servers will have a buffer pool of Memory dedicated specifically to database processes. MyISAM tables can’t be stored in this Memory pool, meaning often times queries to these tables end up writing to swap instead.
So you just looked at your database, and you discovered *gasp* MyISAM tables! Now what? Simple! You can reformat those tables to InnoDB. Follow this guide for more help. Once they’re converted, your queries will run faster and can use the Memory dedicated to InnoDB.
Reduce Autoloaded Data
In WordPress, your wp_options table contains a column called “autoload.” This column tells WordPress whether the data in the corresponding row needs to load on every single page load. A lot of plugins and themes add rows to this table, defaulting to “yes” in that column. And let’s be honest: no, every logged 404 on your site does not need to load on every page. There is some important stuff there though, like your site’s home/siteurl, theme, theme settings, and so on. So it’s good practice to look through this table and see whether the items you’re autoloading really need to be autoloaded. Try this query to locate the longest rows in your wp_options table:
SELECT LENGTH(option_value),option_name FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 20;
Look at the top rows of autoloaded data, and see if there’s any options listed that don’t need to be loaded on every page. If not, set that option’s autoload column to “no” instead.
Get rid of orphaned metadata
When you delete a post, page, image, or comment in WordPress, it removes the post entry from your wp_posts or wp_comments table. But it leaves behind all the data associated with that entry in the wp_postmeta or wp_commentmeta table. Depending on the amount of attributes associated with each entry, this could add up to a lot of “orphaned” metadata that’s simply no longer needed! Try these queries to find the total amounts of orphaned metadata:
SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
And try these queries to delete those orphaned entries:
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
Every few months it’s a good idea to Optimize your tables in MySQL. This action will help reorganize your table and get rid of any wasted space. It will also rebuild the indexes on the table, meaning full table searches will run faster. Read more about optimizing InnoDB databases in MySQL’s documentation.
Add more resources
Last, remember that it’s best practice to ensure all your active tables actually fit in the Memory you have allocated to InnoDB. If you have 1GB Memory dedicated to InnoDB and you have a 6GB database, chances are that you can’t trim your database down enough to fit in that buffer pool. You are simply going to need more Memory. At this point it’s best to have a conversation with your team. It’s time to upgrade your server resources to support your database usage.