How to optimise your website’s database

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:

Unassociated tags

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);

Autosaves

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%”

Trash days

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:

define(‘EMPTY_TRASH_DAYS’, 7);

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

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:

1. Log in to the Magento Admin facility.
2. Then select System > Configuration.
3. Under Advanced, select System.
4. Under Log, select Yes under Enable Log Cleaning.
5. 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.

Indexes

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;

id	first_name	last_name	email	address
1	Andrew	Elliott	ae@example.com	1 Example Road
2	Katie	Gallacher	kd@example.com	2 Example Road
3	Alan	Beales	ab@example.com	3 Example Road
4	Sheila	Willoughby	sw@example.com	4 Example Road
5	Debbie	Elliott	de@example.com	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

id:	1
select_type:	SIMPLE
table:	customers
type:	range
possible_keys:	by_last_name
key:	by_last_name
key_len:	52
ref:	NULL
rows:	2
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

id:	1
select_type:	SIMPLE
table:	customers
type:	ALL
possible_keys:	NULL
key:	NULL
key_len:	NULL
ref:	NULL
rows:	5
Extra:	Using where

Indexes can make a real difference and improve performance dramatically.

Optimize table

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:

Any thoughts?

We’ve only scratched the surface of database maintenance, but I hope this has been of some help for those new to it.

As you can see, database maintenance is another way to achieve a faster site, beyond what we offer already like fast web hosting, a CDN and a Website Acceleration Suite.

If you have any simple database tips to offer, let us know below. Also feel free to contact our support team.

Library photo by Tobias Fischer on Unsplash