How to Optimize MySQL Database Performance on Shared Hosting

When a WordPress site feels slow and all the usual suspects - unoptimized images, missing caching, render-blocking scripts - have been addressed, the database is often what is left. MySQL performance problems are common on sites that have been running for a few years, and they manifest in ways that front-end optimization cannot fix.

Here is how to diagnose and improve database performance on a shared hosting environment.

Signs Your Database Is Slow

A few reliable indicators point to database performance as the root issue:

• The WordPress admin is notably slower than the front end, especially post listings, media library, and plugin pages that run complex queries
• TTFB (Time to First Byte) remains high even after enabling full-page caching on uncached pages (like the admin)
• Pages with dynamic content (search results, WooCommerce shop pages, comment-heavy posts) are significantly slower than static pages
• The Query Monitor plugin reports slow queries taking more than 100ms

Running OPTIMIZE TABLE in phpMyAdmin

Over time, MySQL tables become fragmented - deleted rows leave gaps, indexes grow inefficient, and table files occupy more space than their actual data requires. Running OPTIMIZE TABLE compacts the table and rebuilds its indexes.

In cPanel:

1. Go to Databases > phpMyAdmin
2. Click your WordPress database in the left panel
3. Click Check All at the bottom of the table list to select all tables
4. From the "With selected" dropdown, choose Optimize table
5. phpMyAdmin runs the optimization and reports the results

This is safe to run on a live site, though best done during off-peak hours. Do this every three to six months on active sites.

Cleaning Up Post Revisions

WordPress saves a revision every time you edit a post or page. On a mature site, this can result in thousands of revision records in the wp_posts table - all of them taking up space and slowing down queries.

You can set a revision limit in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

This limits future revisions to five per post. To clean up existing ones, use the WP-Optimize plugin (free), which can bulk-delete old revisions safely with a single click.

Clearing Spam and Trashed Comments

The wp_comments table accumulates spam at a surprising rate on any site with comments enabled. Even if Akismet is catching spam, it holds it in the database rather than deleting it. A site that has been running for three years can have tens of thousands of spam comment records.

In WordPress admin: go to Comments, filter by Spam, select all, and delete permanently. Then do the same for Trash. Also empty the post trash under Posts > All Posts > Trash.

Using WP-Optimize for Regular Maintenance

The WP-Optimize plugin (over one million active installs) handles all of the above in one interface:

• Removes post revisions older than a specified number
• Clears spam and trashed comments
• Removes transient options (expired temporary data stored in wp_options)
• Optimizes database tables
• Can schedule these cleanups to run automatically

The transient cleanup is worth emphasizing. The wp_options table often grows large on sites with many plugins, because plugins store temporary data (transients) there. Expired transients that are never cleaned up accumulate into thousands of rows that slow down every page load, since WordPress reads from wp_options on almost every request.

Object Caching: Redis and Memcached

WordPress queries the database on every page load unless full-page caching serves a cached HTML file. But even with page caching, logged-in users and dynamic pages (WooCommerce, membership sites) still generate database queries.

An object cache keeps the results of database queries in memory (RAM) so identical queries can be served without hitting the database again. Redis and Memcached are the two most common object cache backends.

On shared hosting, access to Redis or Memcached depends on your hosting provider and plan. dotCanada supports Redis on eligible plans - check your plan details or contact support to find out if it is available for your account. If it is, installing the Redis Object Cache plugin connects WordPress to it automatically.

For most small sites on shared hosting, proper table optimization and transient cleanup will provide a noticeable improvement. Object caching becomes more impactful as traffic and content volume grow.

Understanding Shared Hosting Database Limits

On shared hosting, you are sharing MySQL server resources with other customers. Most providers impose soft limits on query execution time, active connections, and CPU usage per account. When your database is inefficient, you hit these limits sooner.

Signs you are hitting database limits rather than just having an inefficient database:

• Intermittent slowness, not consistent slowness
• Occasional "Error establishing a database connection" messages on traffic spikes
• The site is fast right after a maintenance window but slows down throughout the day

If optimization does not solve the problem, the site may have outgrown shared hosting. Upgrading to a VPS gives you dedicated database resources and significantly more headroom. But for most small to medium WordPress sites, the optimization steps above are enough to restore solid performance without changing plans.