Database maintenance for beginners
If you don’t carry out regular database maintenance, your website can slow down over time. This can make your site appear slow to the user and affect search engine results.
Database maintenance is something that everyone who looks after a website (that uses a database) should know about. In this article, I’ll cover WordPress database maintenance, one tip for Magento maintenance and then finish with some general optimisation tips that will apply to any database.
If the whole concept of database maintenance sounds daunting, I understand. But don’t worry: this guide will show the tools you can use and code examples. It’s suitable for anyone.
WordPress database maintenance
It can help to imagine accessing a database as being like trying to find a book in a library. The more books there are in there, the longer it will take to find exactly what you’re looking-for. With databases, unnecessary tables can take up space, making everything more difficult to find.
Deactivated plugins and themes in WordPress databases can leave their traces in the database and take up space. Giving it a regular spring-clean can really help.
We can use a tool called phpMyAdmin to look at database table sizes. If you manage a package in My20i, choose phpMyAdmin and click on the database on the left, you’ll see a list of tables like this:
If you then select by the Size column, you can see which tables within your WordPress install are the largest. Potentially, these could be removed, maintained or reduced in size.
We’d recommend that you take a backup of your database before doing anything, especially if you’re new to this.
WordPress plugins and themes
For example, you can look for tables that relate to deactivated plugins, and remove them as they’re no longer needed.
Some deactivated plugins can have very large tables in place. This is because database tables and data are still in place in case you ever want to reactivate the plugin, and/or theme.
If you don’t think that you’re going to reactivate it, you can remove a table within phpMyAdmin by ticking the box next to it and using the DROP TABLE function from the drop-down:
From time to time, you may have a number of tags that are not associated with any posts. If you’ve removed a large number of posts/articles, these tags may still be present in the database.
These orphan tags can be removed by making SQL queries, which can be run in the second tab in phpMyAdmin, SQL.
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
While autosaves in WordPress can be life-saver, they’re not too kind to your database size. They can be removed with:
DELETE FROM wp_posts WHERE post_type = “revision” AND post_name LIKE “%autosave%”
WordPress does do some clearing-up itself. You can change the frequency of when these ‘trash days’ take place by defining EMPTY_TRASH_DAYS within your wp-config.php file as follows:
The end value is how often, in days, you would like this to happen. Here, the deletion of trash would occur every seven days.
Transients offer a means to store cached data within a database temporarily.
Whilst this can be useful for reducing the amount of queries a site makes, you may often find that WordPress hasn’t removed transients that have expired – those that have specific expiration dates. Expired transients can be removed with the following query:
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')
Magento database maintenance
Magento databases may often increase in size even if you haven’t deliberately added data to them.
This is often happens due to log data being kept by the install that may not be being cleared automatically. In most cases, if you clear this log data you’ll see a large reduction in file size.
To prune log data within Magento, you’ll need to:
- Log in to the Magento Admin facility.
- Then select System > Configuration.
- Under Advanced, select System.
- Under Log, select Yes under Enable Log Cleaning.
- Set the frequency for which you would like log data retaining for under Save Log, Days.
For most users, we would recommend only retaining log data for up to a week, which is standard practice.
Going forward, Magento will only retain log data for the length of time set. If you find that your database is still larger than you want, then consider reducing the number of days that it’s retained-for.
General database optimisation
Now we’ll look at some general ways to make databases – and therefore your website – respond faster.
Going back to the library metaphor, you don’t want to check the spine of every book to find the one you need. An index can make a massive difference when querying large datasets.
If a table contains hundreds of thousands of records, even the fastest of database servers will struggle to perform an intended query if the database isn’t indexed properly.
An index can be created with the CREATE INDEX statement after a table has been created, or it can be created at the time of the table creation with the CREATE TABLE statement.
For example, let’s imagine we have the following table (in real life, you’d probably have many more records):
select * from customers;
|1||Andrew||Elliottfirstname.lastname@example.org||1 Example Road|
|2||Katie||Gallacheremail@example.com||2 Example Road|
|3||Alan||Bealesfirstname.lastname@example.org||3 Example Road|
|4||Sheila||Willoughbyemail@example.com||4 Example Road|
|5||Debbie||Elliottfirstname.lastname@example.org||5 Example Road|
If we want to find Debbie Elliott’s email address, our query would need to go through every record until it’s able to find the customer with the first and last name we are looking for. This is inefficient.
To make the retrieval of records faster, we can create an index, or indexes:
CREATE INDEX by_last_name ON customers (last_name);
If we then use EXPLAIN, we can check the index or indexes being used for the SELECT statement we intend to run:
EXPLAIN SELECT * FROM customers WHERE last_name LIKE 'Elli%'\G
|Extra:||Using index condition|
Going forward, MySQL will be able to rely on this index to retrieve the record(s) more quickly, by checking fewer rows.
We can contrast this with the first_name table. As the following EXPLAIN shows, MySQL is examining every row in the table (five), whereas our previously created index is only examining two. Fewer rows means a more efficient search:
EXPLAIN SELECT * FROM customers WHERE first_name LIKE 'Deb%'\G
Indexes can make a real difference and improve performance dramatically.
The OPTIMIZE TABLE command can be useful in the reduction of table sizes. In the library analogy, it’s a bit like re-organising the shelves so that all the books are less spread out, so you don’t have to walk so far between aisles.
OPTIMIZE TABLE can be thought of as a defragmentation, and will essentially recreate the table and reclaim any unused space.
For example, if a large part of a table has been removed (large numbers of rows deleted), these are still maintained, with subsequent operations still “seeing” those rows.
You can use OPTIMIZE TABLE to reclaim this space.
To achieve this, log in to PHPMyAdmin.
From this point, we can then select tables for which we wish to optimise, and then select the option available from the dropdown menu:
We’ve only scratched the surface of database maintenance, but I hope this has been of some help for those new to it.
If you have any simple database tips to offer, let us know below. Also feel free to contact our support team.