Database Performance Basics for MySQL-Powered Websites

Most dynamic sites, including WordPress, Joomla and online shops, keep their content and settings in a database, usually MySQL or MariaDB. Each page view triggers dozens or hundreds of queries. When the database is slow, every uncached page is slow, no matter how well optimised your images and scripts are.

You do not need to be a database administrator to make a difference. This article covers what slows databases down, how to see it, and the changes that help most.

How a page uses the database

When a visitor opens a WordPress post, the code asks the database for the post, its metadata, the menu, widget settings, comments, plugin options and more. Each request is a query. A query is fast when the database can find the rows it needs quickly, and slow when it must scan large tables, sort big result sets or wait for locked rows.

Signs of a database bottleneck

• Time to first byte is high for uncached pages but low for static files.
• The admin area is much slower than the public site.
• Slowness appears under load, such as during imports, backups or traffic peaks.
• A particular page, such as a search, a filter or a report, is far slower than the rest.

Finding slow queries

1. Use a query monitoring plugin on a staging copy. Query Monitor, for WordPress, lists queries per page with timings and the plugin or theme responsible.
2. Enable the slow query log on a VPS or dedicated server. MySQL can record any query taking longer than a threshold you set.
3. Use EXPLAIN in front of a SELECT statement to see how the database plans to run it. Warnings about full table scans point to missing indexes.

Indexes: the biggest lever

An index works like the index of a book: instead of reading every page to find a word, the database jumps to the right place. Queries that filter or sort on columns without indexes force a scan of the whole table, which gets slower as tables grow. Core WordPress tables are already well indexed, but custom plugin tables and custom queries often are not.

Do not add indexes randomly. Each one speeds reads but slows writes and uses disk space. Add an index when EXPLAIN and slow-query evidence shows a specific query needs it, and back up the database first.

Clean up bloat

Databases accumulate leftovers over time:

• Post revisions: WordPress stores a copy each time you save. Limiting revisions and clearing old ones reduces table size.
• Transients: temporary cached values that can expire but linger.
• Spam and trashed comments, orphaned metadata: rows that no longer belong to anything.
• Autoloaded options: WordPress loads certain options on every request. If plugins store large values there, every page pays the cost. Review the largest autoloaded options and remove data left by plugins you no longer use.
• Tables of uninstalled plugins: deactivating a plugin often leaves its data behind.

Always take a backup before deleting anything from a database, and prefer reputable tools or plugin features to hand-written deletion queries.

Cache so you query less

The fastest query is the one you do not run. Page caching removes the database from most anonymous page views altogether, and a persistent object cache with Redis or Memcached keeps repeated results in memory. Our article on server-side caching explains how these layers fit together.

Sensible maintenance

• Keep MySQL or MariaDB, and PHP, on supported versions.
• Use InnoDB for tables, which supports row-level locking and crash recovery.
• Schedule heavy tasks, such as backups and imports, outside busy hours.
• Limit what plugins can run on every request, especially ones that make large queries on the front end, such as related-post engines.
• Monitor database size over time. Sudden growth often means a misbehaving plugin or log table.

When it is a resource problem

Sometimes the code is fine and the server is short of memory or disk speed. On shared hosting, the database's resources are limited by the plan. If your dataset or traffic has outgrown it, a VPS lets you tune MySQL settings such as the buffer pool size, which controls how much data can stay in memory. Dedicated servers go further. Do application-level fixes first, because they are cheaper and help whichever plan you use.

A safe clean-up routine

1. Take a full backup of the database and files, and confirm you can restore it.
2. Work on a staging copy first if you can, so any mistakes do not touch the live site.
3. Limit revisions in your configuration, then delete old revisions with a reputable tool.
4. Remove expired transients and spam comments.
5. Look at the biggest tables in phpMyAdmin or Plesk's database tools. Unexpectedly large tables usually point to a plugin that logs everything.
6. Run the optimise or repair function for tables only when needed. It reclaims space after big deletions but is not a regular cure for slowness.
7. Measure time to first byte again to confirm that the work helped.

Our knowledge base covers day-to-day panel tasks such as accessing database tools in Plesk. Repeat this routine every few months, and sooner after removing a big plugin.

Where to run a data-heavy site

Zebra Hosting Cloud provides Plesk on every plan, with database management built in. If you are outgrowing shared resources, look at the VPS plans, delivered by the team within 24 hours.