The WordPress database is often the last thing on your mind when it comes to optimizing and tweaking your site but I can’t stress enough how important it is to keep your data in order.
The busier your site the more important it is to optimize and cleaning it up on a regular basis.
Before making any changes it is always a good practice to back up your database.
Login in to phpMyAdmin
In order to follow this guide, first we need to login to phpMyAdmin.
If you are using Cpanel which is the most commonly used account manager, simply enter the following.
You may see a warning about an untrusted connection but proceed as normal.
Enter your login details and then you will see you Cpanel account manager.
Once logged in proceed to the section named Databases.
Log in to the Database
In most cases a new tab will open up on your browser and automatically log you in.
Once logged in you will the following information.
On the left your databases
Here you will see a list of all your database.
Above your databases are some small menu Icons.
Across the top
A variety of menus which will change options according to where in the database you are.
In the middle
This is your information panel, all the information related to where you are in the database is displayed here.
Taking a look at our Database
To view your database just select the database from the list on the left hand side. In this case it’s called _wrdp1, this is the affix the full database name is your username_database_name.
Once select the typical default WordPress database will have the following tables.
Click to view a larger image
From left to right we can see the following:
Table names – the name given to a specific table which contains data for specific aspects of your websites.
For example – wp_options contains the information related to the settings found in WordPress Admin.
Action – displays a series of options and commands that can be executed. You can safely select Browse, structure and search but avoid Insert, Empty and Drop unless you know what your doing.
Rows – This tells us how many rows of data are stored on each table.
Type – displays the database engine in use for the current table. MyISAM being the default but InnoDB is also a commonly used database engine.
Collation – displays the current collation which is related to character sets. A collation is a set of rules for comparing characters in a character set. You may need to change this sometimes as many database come with latin1_swedish_ci and this is known to cause problems with some characters. I will show you how to change that further down.
Why is latin1_swedish_ci the default collation?
Same reason Microsoft SQL Server’s default language is us_english, it’s because the authors of MySQL are Sweedish.
In the case of the database above, it’s set to utf8_unicode_ci to allow for some funky characters not allowed in latin1_swedish_ci.
size – tells us how big the particular table is. Notice is says “KiB” “MiB” instead of “kB” or “MB”
Whats the difference between KiB and kB?
Kilobytes can mean two different things, it can mean 1024 (2^10) bytes or it can mean 1000 (10^3)
kibibyte (KiB) has only 1 value thus streamlining its meaning.
Overhead - in simple terms this is junk.
A longer explanation would be - Overhead in phpMyAdmin is calculated by ‘Data_free’ column returned by SHOW TABLE STATUS.
Data_free: The number of allocated but unused bytes.
So basically when you delete something the space is still allocated to the table and can be freed by optimizing those tables which I will show you further down.
Optimizing your Database tables
In order to optimize our tables we should first look at what we can clean up.
Common tables that can be clean are:
WordPress saves post revisions to your database every (60 seconds). This means if you like me, take time writing each post you are going to accumulate are large number of revisions which in turn increases the size of the wp_posts table.
Looking at the example database wp_posts table you can see some overhead.
So this tells me I have
- 319 rows
- 703.8 KiB (total table size)
- 60.6 KiB Overhead.
To clear the revisions open up the query window located next to the home Icon (top left) see Figure 2
Once opened, enter the following command in to the query window like so.
DELETE FROM wp_posts WHERE post_type = "revision";
Then simply click GO.
The server will run the SQL query and upon completion you will be notified with a green bar indicating it ran successfully with some information.
Now when we look at our wp_posts table we will see that the overhead has increased.
Now we have
- 243 rows
- 703.3 KiB (total table size)
- 303.3 KiB Overhead
To clear the overhead for wp_posts as well as all other tables select the Check All found below all the tables and first select from the drop down menu repair table.
Again you will be notified when it has completed.
Next click on the Home Icon (top left) and go back to your database again.
Re-select all tables and this time select Optimize table as seen in the image above.
Then when you go back to you database you will see the overhead has been cleared.
All you tables are now optimized.
Lets take a look at a before and after.
|Total number of rows||2411||2329|
|Total table size||1.8 MiB||1.5 MiB|
|Total Overhead||379 KiB||0|
Note - for the total overhead I factored the added overhead from clearing the post revisions.
Considering this is a very small database the space saved is significant and if you scale it up to a database 10 or even a 100 times larger than this you can see where problems begin to arise.
A recent database I worked on was 1.3Gb after optimization it was 98MiB which was a whopping 13 fold reduction.
How can we reduce the total amount of Excess data accumulating in our database?
Once way to reduce the amount of junk that accumulates in our database is to control the number of post revisions or turn them off altogether.
To do that we can add a couple of lines to wp-config found in the root of your site. Same location as wp-content, wp-admin etc.
Set Max number of Post revisions
Add the following to your wp-config file.
You can add this near the top after the opening <?php tag.
define( 'WP_POST_REVISIONS', 3);
This will store a maximum of 3 revisions per post.
Turn OFF post revisions
To turn off post revisions altogether simply add this to your wp-config.
define( 'WP_POST_REVISIONS', 0);
Clear out Spam Comments
Using this query you can quickly clear out all spam comments.
DELETE FROM wp_comments WHERE comment_approved = 0
Other Methods of Reducing Clutter from your Database
Many WordPress plugins add huge amounts of data to your database.
These include Plugins that tracks stats.
- Akismet (bloats the wp_commentmeta table)
- Pretty link (saves each hit to the database, In the pretty link options you can clear stats older than 90 or 30 days.
- Popular post plugins (store data such as page view for each post to calculate popularity, best to use number of comments as a factor as this information exists naturally in the database.
- Visitor stat plugins ( stat plugins can slow down your site anyway, so its best to use external programs like google analytics.)
Now you know how to clean up your mess. Let us know what your gains and savings are.