How do I optimise and reduce the size of my database?
Database maintenance is something that every database/web administrator should practise. An optimised and well-managed database can be the difference between a responsive website, and one that appears sluggish, simply because queries are able to be fetched far faster.
Magento
Magento databases may often increase in size, with no discernible, or apparent reason as to why!
More often than not, this happens because of log data retained by the install that may not be being automatically cleared.
It's common to see a large reduction in the size of a database after cleaning log data.
1) To prune log data within Magento, you'll need to 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 wanted, a reduction in the number of days may be needed.
WordPress
Over time, a WordPress database can become particularly bloated.
During the life of a WordPress install, deactivated plugins and themes, amongst a number of other things, can take up unnecessary space. Removing them can increase response times, simply because WordPress is able to query the database and its tables far more quickly. It doesn't have to sift through data that is no longer required, or large, cluttered datasets.
phpMyAdmin can be used to sort database tables by size (by selecting the Size column):
By doing this, you can see which tables within your WordPress install are the largest, and which potentially need removing, maintaining, or simply reducing in size.
Plugins: For example, we can look for tables that relate to deactivated plugins, and remove them as they’re no longer required. Some deactivated plugins can have very large tables in place. This is because database tables and data are still in place should you ever wish to reactivate the plugin, and/or theme in question.
You can remove a table within phpMyAdmin by using the DROP TABLE function from the available 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 with the following queries:
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: Every time you write a post or an article within WordPress, the in-built autosave feature will make a save within your database. Depending on what you’re writing, these autosaves can be quite large. Therefore, should these autosaves no longer be required, you can make quite a reduction in database size by removing these. Autosaves can be removed with the following query:
DELETE FROM wp_posts WHERE post_type = “revision” AND post_name LIKE “%autosave%”
Trash days: Another useful trick is to increase the frequency in which trash is automatically deleted from your WordPress installation. This can be achieved 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. So in this instance, deletion of trash would occur every seven days.
Transients: Transients offer a means to store cached data within a database temporarily (thus their name).
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; transients having specific expiration dates. Consequently, this can cause the database to become bloated.
Expired transients can be removed by performing the following query:
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')
General Optimisation
Indexes
Indexes can be extremely useful for those with large databases, querying large datasets. If created and used correctly, indexes can make retrieval of records faster.
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 us imagine we have the following table (albeit, with many more records):
If we wish to obtain the email address for Debbie Elliott, our query would need to go through every record until it is 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
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, the latter being much more efficient:
EXPLAIN SELECT * FROM customers WHERE first_name LIKE 'Deb%'\G
So, as you can see, when used correctly, 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. 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 for the database in question via Manage Hosting > Manage > 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:
