As your website grows so do problems that might arise. A lot of the times with WordPress is that it just slows down. Recently one of the problems we ran into was a slow time to first byte or TTFB. This appears to just be a slow loading site and most other sources will tell you to look at plugins or themes to find a problem. After much digging, tracing and head scratching the solution presented itself.

As you add more functionality your database will continue to grow and add more options. Some of these wind up in your WP_OPTIONS table inside your database. Well mostly harmless, sometimes this table grows to be very large and contains many “Transient” options that are set to auto load meaning every time you load a page this option has to be processed. As you can image as more of these options have to be loaded the slower your site will respond and eventually you will come to a screeching halt. Let’s look at how to fix it.

Step 1 – Login to your Server

This guide requires that you have SSH access to your server. Login through your normal method. I typically just switch to the root user once I am logged in.

$ sudo -s

We need to login to the database. Now this guide assumes that your database runs on the same server as your website. This guide should be easily modified if your database runs separate like the websites at 45Squared

First lets login to the database. If your database has a separate host add in host tag option.

$ mysql -u [Your MySQL User] -p

Once logged in select your database

use [Database Name];

Lets first look at the size of each table in our database with the following query.

SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;


This query will return a table showing all the tables in your database sorted by size with the largest at the top. Typically the largest will be wp_postmeta. This is normal especially in e-commerce websites as you will have meta information for each product in your catalog. But let’s look at the wp_options table. Ideally this table shouldn’t be much larger than 10MB. It might be but not typically.

Let’s look at everything that is set to auto load inside the wp_options table with the following query.

SELECT * FROM `wp_options` WHERE `autoload` = 'yes';

Now if you have anything like the sites we work with this is going to be a mess. So lets just focus on our transients as they are safe to remove as plugins will generally re-create them as needed. NOTE: Always take a backup before removing anything out of your database.

Run this query to display all transients that are set to auto load.

SELECT *
FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%';

More than likely this will also display a lovely output of various options that is unreadable. Lets keep filtering down to find something usable with the next query.
SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' and `option_name` LIKE '%transient%' ORDER BY option_value_length DESC LIMIT 10;


Now this will display our top ten biggest transient database entries. Each item should show some correlation to a plugin you have installed. If not you might have a bigger problem at hand and you should contact us immediately.

Once we’ve established that our transients are in fact coming from plugins we have willingly installed lets just delete them all. Once again, if you haven’t done a backup now is the time to do so. Up until this point we have just been poking around in our database now we are going to delete things.
DELETE
FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%';

Please note that the %transient% can be modified to delete other things inside the table so be very careful when you type it in.

After this query runs your table should be much smaller and your website should load much smoother.

This guide shows you how to view and remove transients to speed up your WordPress installation.

If you are still having problems feel free to reach out to our team for assistance!

If this guide has been helpful for you and your team please share it with others!