Database maintenance is crucial for every database or web administrator. An optimised and well-managed database can significantly enhance your website’s responsiveness. CP CLOUD HOSTING ensures your site performs efficiently by reducing query times.

Optimising Magento Databases #

Magento databases can often increase in size without any clear reason. This usually happens due to log data retained by the system, which might not be cleared automatically. Cleaning this log data can substantially reduce your database size.

1. Log in to the Magento Admin.
2. Go to System > Configuration.
3. Under Advanced, select System.
4. Enable log cleaning by selecting Yes under Enable Log Cleaning.
5. Set the log retention period under Save Log, Days. We recommend retaining log data for up to a week.

By enabling log cleaning, Magento will only retain log data for the specified period. If your database is still too large, consider reducing the retention period.

Optimising WordPress Databases #

WordPress databases can become bloated over time due to deactivated plugins, themes, and other unused data. Cleaning up these elements can improve your site’s performance.

• Using phpMyAdmin: Sort database tables by size to identify large tables that may need attention.
• Removing Deactivated Plugins: Look for tables related to deactivated plugins and remove them if they are no longer needed.
• Deleting Unassociated Tags: Remove orphan tags using 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);

Removing Autosaves: Remove unnecessary autosaves with this query:

DELETE FROM wp_posts WHERE post_type = "revision" AND post_name LIKE "%autosave%"

Emptying Trash: Set automatic trash deletion frequency by adding this line to your wp-config.php file:

define('EMPTY_TRASH_DAYS', 7);

Removing Expired Transients: Clear expired transients with this query:

DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%')

General Optimisation

Creating Indexes: Indexes can significantly speed up data retrieval in large databases. Create an index using the CREATE INDEX statement:

CREATE INDEX by_last_name ON customers (last_name);

Using the OPTIMIZE TABLE Command: This command can defragment tables and reclaim unused space:

OPTIMIZE TABLE table_name;

Use phpMyAdmin to optimise tables by selecting the tables you want to optimise and choosing the option from the dropdown menu.

By following these steps, you can maintain an efficient and responsive database for your website hosted with CP CLOUD HOSTING.