When a site or app feels slow, the database is often the real bottleneck, not the code. The good news is that database speed problems usually come from a short list of fixable causes. Here is how to find what is slow and fix it, highest-impact change first.
1. Find the slow queries first
Do not guess. Turn on the slow query log to see exactly which queries cost you:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
This logs any query taking over 1 second. After some real traffic, the log shows your worst offenders. Fix those before touching anything else, because one bad query usually causes most of the pain.
2. Add indexes (the biggest win)
A missing index is the number one cause of slow queries. Without one, the database scans every row to find a match; with one, it jumps straight to it.
Run EXPLAIN in front of a slow query:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
If you see type: ALL and a high row count, it is doing a full table scan. Add an index on the column you filter or join on:
CREATE INDEX idx_customer ON orders (customer_id);
Index the columns used in WHERE, JOIN and ORDER BY. Do not index everything, though, because every index slows down writes. Add them where queries actually need them.
3. Stop selecting more than you need
SELECT * pulls every column, even ones you never use. Select only the columns you need, and always use a LIMIT when you do not need the full result set. Small changes here add up under load.
4. Tune the buffer pool
For InnoDB (the default engine), the single most important setting is innodb_buffer_pool_size: how much data and index MySQL keeps in RAM. On a dedicated database server, set it to roughly 60 to 70 percent of available RAM. Too small means constant disk reads; too large starves the rest of the system.
5. Keep it tidy
- Remove old data you do not need, or archive it out of hot tables.
- Run
OPTIMIZE TABLEon heavily-churned tables now and then. - Keep MySQL or MariaDB on a current version, because newer releases are genuinely faster.
6. The hardware floor
Databases love fast storage and RAM. On slow, oversold shared hosting, even a well-tuned database struggles. NVMe storage and guaranteed RAM remove that ceiling. See VPS vs shared vs dedicated hosting.
FAQ
Why is my database slow?
Most often a missing index causing full table scans, then a too-small buffer pool, then SELECT * pulling more than needed. Use the slow query log and EXPLAIN to confirm.
Will adding indexes always make things faster?
Reads, yes. But each index slows inserts and updates and uses space, so index the columns your queries actually filter or sort on, not every column.
How much RAM should I give MySQL?
On a dedicated database box, set innodb_buffer_pool_size to about 60 to 70 percent of RAM, leaving room for the OS and other services.
Need a database that is not fighting its neighbors? Pick a Vastrox VPS with NVMe storage and guaranteed RAM.