How to Fix Slow MySQL Queries on a VPS

A slow website does not always mean you need a more powerful server. Sometimes, the real problem is hiding inside your database.

Your website may load normally in the morning but become frustratingly slow when more visitors arrive. Product searches may take several seconds. Your WordPress dashboard might freeze, or customers may struggle to complete checkout.

These problems can happen when MySQL queries take longer than necessary.

The good news is that you can often improve database performance without upgrading your VPS. The first step is understanding what is slowing it down.

This guide explains how to identify slow MySQL queries, improve database efficiency, and make better use of your existing server resources.

What Are Slow MySQL Queries?

MySQL is a database management system used by many websites and applications. It stores information such as customer accounts, blog posts, product details, and order records.

Whenever your website needs information, it sends a query to the database.

For example, an online store might request a list of products in a particular category. MySQL processes that request and returns the matching information.

A query becomes a performance problem when it takes longer than expected or consumes too many server resources.

Imagine a library where every book is organised by category. Finding one title should be straightforward.

Now imagine searching for a book in a library with no catalogue. You might need to check every shelf.

A poorly optimised database can behave similarly.

Slow queries can cause:

Longer website loading times
Delayed searches and filtering
High CPU usage
Increased memory consumption
Slow WordPress administration
Database connection timeouts
Poor performance during busy periods

However, a slow query is not always the only cause of these symptoms. Network delays, application code, storage problems, and server resource limits can produce similar results.

That is why proper diagnosis matters.

Why Do MySQL Queries Become Slow on a VPS?

Several factors can affect database performance. Some are related to the database itself, while others involve the application or hosting environment.

1. Missing or Inefficient Database Indexes

Indexes help MySQL locate information without examining every row.

For example, imagine a customer database containing thousands of email addresses.

If the application frequently searches by email, an appropriate index can help MySQL find the matching record more efficiently.

Without a suitable index, the database may need to examine many records.

However, adding indexes to every column is not a good solution. Excessive indexing consumes storage and can slow down inserts and updates.

The goal is to create indexes that support real query patterns.

2. Queries That Retrieve Too Much Data

Some applications request more information than they need.

Consider this query:

SELECT * FROM products;

It requests every column from every product record.

If the application only needs product names and prices, a more focused query would be:

SELECT name, price
FROM products
LIMIT 20;

This example reduces the amount of information returned.

In a real application, you should also use appropriate filtering and ordering. A LIMIT clause alone does not guarantee that MySQL will perform less work.

3. Large or Poorly Maintained Tables

As websites grow, databases accumulate information.

An e-commerce website may store years of orders, sessions, logs, and temporary records.

Large tables are not automatically slow. MySQL can handle substantial datasets when queries and indexes are designed correctly.

Problems arise when applications repeatedly scan unnecessary records or process outdated information.

4. Too Many Database Requests

A website might execute dozens of queries to generate a single page.

Some applications repeat nearly identical queries rather than reusing results.

This can become especially noticeable when traffic increases.

Even individually fast queries can create substantial server load when executed thousands of times.

5. Inappropriate MySQL Configuration

MySQL uses memory for caching, query processing, and database operations.

Configuration settings affect how efficiently these resources are used.

For example, an undersized InnoDB buffer pool may increase disk reads. Excessive connection limits can also create memory pressure.

But increasing every memory setting is dangerous. Your VPS also needs resources for the operating system, web server, PHP, and other applications.

Configuration changes should always reflect the actual workload.

How to Identify Slow MySQL Queries on Your VPS

Before changing your database, find out which queries are causing problems.

Step 1: Check Server Resource Usage

Start by checking whether your VPS is experiencing CPU, memory, or disk pressure.

On a Linux VPS, the following commands can provide useful information:

top

Or, if installed:

htop

For memory usage:

free -h

For disk space:

df -h

These commands help identify obvious resource problems.

For example, if MySQL is using substantial CPU during slow page loads, database activity deserves further investigation.

However, high CPU usage alone does not prove that a particular query is inefficient.

You need database-level evidence.

For a broader server troubleshooting approach, explore DigitalBerg’s article, How to Fix High CPU Usage on a Linux VPS.

Step 2: Enable the MySQL Slow Query Log

The slow query log records statements that exceed a configured execution-time threshold.

It is one of the most useful tools for investigating database performance.

First, inspect the current settings:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

For a temporary diagnostic session, an administrator can enable logging:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

The second command sets the threshold to one second for new sessions.

Existing connections may retain their previous session setting. These changes may also be lost after a restart unless saved through the appropriate server configuration.

A one-second threshold is only a starting point. Some applications need much faster queries, while others perform legitimate long-running tasks.

Important: Query logs can contain sensitive information and grow quickly. Restrict access, monitor disk usage, and disable or adjust logging when the investigation is complete.

Step 3: Analyse the Slow Query Log

Once the database has collected useful information, examine the slow queries.

MySQL provides a utility called mysqldumpslow.

For example:

mysqldumpslow -s t -t 10 /path/to/slow-query.log

Replace the path with the actual slow query log location.

This command helps identify queries with high average execution times.

You can also investigate frequently repeated queries. A query taking 100 milliseconds might be more expensive overall than a two-second query if it runs thousands of times.

Focus on both execution time and frequency.

Step 4: Use EXPLAIN to Understand Query Execution

MySQL provides the EXPLAIN statement to show how it plans to execute a query.

For example:

EXPLAIN
SELECT id, name
FROM products
WHERE category_id = 5;

The output can reveal whether MySQL plans to use an index and how many rows it estimates examining.

Pay attention to fields such as:

type: the access method
key: the selected index
rows: estimated rows examined
Extra: additional execution information

A full table scan is not automatically bad. It may be efficient for a small table or a query that needs most records.

For MySQL 8.0.18 and later, EXPLAIN ANALYZE can provide actual execution measurements. Remember that it executes the query, so use caution with expensive statements on production systems.

7 Practical Ways to Improve MySQL Query Performance

Once you identify the cause, you can begin making targeted improvements.

1. Add the Right Database Indexes

Indexes are often one of the most effective ways to improve frequently executed queries.

Suppose your application regularly searches orders by customer ID.

SELECT id, total
FROM orders
WHERE customer_id = 125;

An index on customer_id may help:

CREATE INDEX idx_customer_id
ON orders(customer_id);

Before adding it, check whether an equivalent or suitable composite index already exists.

Test the query plan before and after the change.

Remember: Index creation can consume resources and may affect concurrent database operations. Schedule significant changes carefully.

2. Avoid Unnecessary SELECT * Queries

Retrieving every column is convenient during development, but it may waste resources in production.

Instead of:

SELECT * FROM customers;

Request only the fields the application needs:

SELECT id, first_name, email
FROM customers
WHERE status = 'active'
LIMIT 50;

This can reduce data transfer and application memory usage.

It also makes the query’s purpose clearer.

3. Improve Queries That Use JOIN

Many applications combine information from multiple tables.

For example, an online store might retrieve orders together with customer details.

SELECT o.id, o.total, c.name
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
WHERE o.status = 'pending';

A poorly designed join can require MySQL to examine unnecessary rows.

Check whether the columns used in joins and filters have suitable indexes.

Also review whether the query returns more records than the application needs.

Use EXPLAIN to confirm the actual execution plan instead of assuming every join requires a new index.

4. Review Your InnoDB Buffer Pool

The InnoDB buffer pool stores frequently accessed data and indexes in memory.

When useful database pages are already in memory, MySQL may avoid additional disk reads.

The setting is controlled by:

innodb_buffer_pool_size

To inspect its current value:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

A larger buffer pool can help some workloads, but it is not always the right choice.

On a VPS running MySQL alongside PHP, Nginx, Apache, or other applications, allocating too much memory to the database can cause swapping or out-of-memory failures.

Monitor memory usage and database performance before changing the setting.

5. Reduce Repeated Database Requests With Caching

Some database queries return information that rarely changes.

For example, a website may repeatedly request the same navigation menu, product category list, or site settings.

Caching can reduce the need to run these queries for every visitor.

Depending on your application, useful options include:

Application-level caching
Redis object caching
Page caching
Caching frequently requested API responses

For WordPress websites, persistent object caching may reduce repeated database work when properly configured.

Caching requires a sensible expiration or invalidation strategy. Otherwise, visitors may receive outdated information.

Also, caching should not hide serious query problems that need fixing.

6. Remove Unnecessary Database Records Carefully

Applications often accumulate records that are no longer useful.

Examples include expired sessions, temporary data, old logs, and obsolete application records.

Removing unnecessary data may reduce storage requirements and simplify some database operations.

However, deleting large amounts of data can generate substantial disk activity and transaction logs.

Before cleaning a production database:

Create and verify a restorable backup.
Identify exactly which records are safe to remove.
Check application dependencies and retention requirements.
Delete large datasets in manageable batches.
Measure the results afterward.

Never remove customer, financial, or application records simply because they are old.

For additional guidance on recovery planning, read DigitalBerg’s website backup and server snapshot comparison.

7. Keep MySQL and Your Application Updated

Database performance can also depend on software versions.

Supported MySQL releases may include optimizer improvements, bug fixes, and performance-related changes.

Application updates can fix inefficient database operations too.

However, database upgrades should never be treated as a quick experiment.

Check compatibility, back up your data, test the upgrade in a staging environment, and plan a rollback procedure.

A stable, supported database version is more valuable than an untested upgrade.

How to Tell Whether Your Changes Worked

Database optimisation should produce measurable improvements.

Do not rely only on whether a website feels faster.

Record a baseline before making changes.

Useful measurements include:

Average and high-percentile query execution time
Number of slow queries
Query execution frequency
Database CPU utilisation
Disk I/O activity
Application response time
Database connection errors

After each change, compare the results under similar traffic conditions.

For example, suppose a frequently executed product lookup previously took 400 milliseconds and now takes 40 milliseconds.

That is a meaningful improvement for that query.

However, it does not automatically mean every page on the website will become ten times faster.

The overall result depends on how much time the application spends executing that query.

Make one meaningful change at a time. This helps you identify which optimisation actually improved performance.

Common MySQL Optimisation Mistakes to Avoid

Database tuning can create new problems when changes are made without proper testing.

Increasing Every Memory Setting

More memory allocation does not always mean better performance.

An oversized database configuration can leave insufficient memory for other services.

Adding Indexes to Every Column

Indexes help certain queries but create overhead for writes and storage.

Use query plans and workload data to decide which indexes are useful.

Deleting Records Without Backups

Removing data without understanding application dependencies can break important features.

Always verify backups and test cleanup procedures.

Changing Production Settings Without Monitoring

A configuration that works well on one server may perform poorly on another.

Monitor results and maintain a rollback plan.

Assuming Faster Hosting Fixes Poor Queries

More powerful hardware can reduce some bottlenecks, but inefficient SQL may continue wasting resources.

Optimisation should come before unnecessary infrastructure spending.

When Should You Consider Upgrading Your VPS?

Not every database problem can be solved through software optimisation.

A VPS upgrade may become necessary when your workload genuinely exceeds the available resources.

Consider reviewing your hosting capacity when:

CPU usage remains consistently high after query optimisation.
The database working set is much larger than available memory.
Storage performance limits legitimate database activity.
Application traffic and concurrent requests continue growing.
You need stronger resource isolation or a different infrastructure design.

Before upgrading, check whether the bottleneck is CPU, RAM, storage, or application architecture.

Choosing the right resource matters more than simply selecting a larger plan.

If you are comparing server configurations, DigitalBerg provides VPS hosting solutions with different resource options.

The right hosting environment should match your application’s actual requirements.

A Simple MySQL Performance Checklist

If your website is running slowly, follow this order:

Check CPU, memory, disk space, and server activity.
Enable slow query logging for a controlled investigation.
Identify frequently executed or expensive queries.
Use EXPLAIN to inspect their execution plans.
Add or adjust indexes where justified.
Reduce unnecessary data retrieval.
Review caching opportunities.
Check MySQL memory settings.
Remove unnecessary records safely.
Compare performance before and after each change.

This approach helps you avoid random configuration changes.

It also makes troubleshooting easier when problems return.

Frequently Asked Questions

Why is MySQL slow even when my VPS has enough RAM?

Available RAM does not guarantee efficient queries. Missing indexes, inefficient joins, repeated database requests, and disk I/O can all affect performance. Start by examining the slow query log and execution plans.

Can I fix slow MySQL queries without upgrading my VPS?

Yes, in many cases. Query optimisation, appropriate indexing, caching, and careful configuration changes can improve performance without additional hardware. Results depend on the underlying cause.

Does adding an index always make a MySQL query faster?

No. An index helps only when it suits the query and the data. Some queries benefit from table scans, while unnecessary indexes increase storage and write overhead.

What is the MySQL slow query log?

It is a diagnostic log that records statements exceeding a configured execution-time threshold. Administrators use it to identify database operations that need further investigation.

Is MySQL or MariaDB better for VPS hosting?

Both can support demanding applications. The better choice depends on application compatibility, supported versions, features, and operational requirements. Their configuration and optimisation behaviour can differ.

How much RAM should I allocate to MySQL on a VPS?

There is no universal amount. The correct allocation depends on database size, workload, available memory, and other services running on the VPS. Measure resource usage before adjusting memory settings.

Can slow MySQL queries affect website SEO?

They can contribute to slow server responses and poor user experience. However, SEO performance depends on many factors, and database optimisation alone does not guarantee improved rankings.

Final Thoughts

Slow MySQL queries can make an otherwise healthy website feel unreliable.

But upgrading your VPS should not be the first response.

Start by identifying expensive queries. Check execution plans, improve indexing, reduce unnecessary requests, and review caching opportunities.

Small, well-tested changes can sometimes deliver substantial improvements.

If your website continues to struggle after optimisation, review whether your hosting resources match the actual workload.

Need help choosing suitable hosting infrastructure?

Explore DigitalBerg VPS Hosting or contact the DigitalBerg team to discuss your server requirements.