How to Speed Up a Slow Database-Driven Website
%203.png&w=256&q=75){kind=small}
Find the Real Slow Queries First
Every "slow database" complaint eventually turns out to be two or three specific queries, not the database engine itself. Guessing which ones — usually by staring at plugin lists or recently edited code — wastes hours that MariaDB will tell you for free. The slow query log records every query that takes longer than a threshold you set, with the exact SQL, the time it took, and how many rows it examined.
Set long_query_time to 1 second to start; on a busy WooCommerce store you will often want to drop it to 0.5 once the obvious offenders are fixed, so the smaller ones stop hiding underneath. Let it run through a normal traffic period, then sort the log with mysqldumpslow or pt-query-digest to rank queries by total time consumed rather than by a single slow run — a query that takes 200ms but fires 500 times a minute is usually a bigger problem than one slow query that runs once an hour. That ranked list is your actual to-do list for the rest of this article, not a hunch about "the database feels slow."
Read EXPLAIN to Find Missing Indexes
Once you have a real query from the slow log, prefix it with EXPLAIN and MariaDB will show you the execution plan it actually chose — which index it used, if any, and roughly how many rows it had to scan to answer the query.
The type column is the fastest thing to scan: ALL means a full table scan, index means it scanned an entire index instead of the table (better but still not targeted), and ref or eq_ref mean it went straight to the matching rows using an index. The rows column is the estimated number of rows examined to produce the result — on the same query, dropping from 480,000 rows to 1 row is the entire difference between a page load and a timeout. This exact pattern is the single most common cause of a slow WordPress or WooCommerce site: wp_postmeta and wp_options grow into the hundreds of thousands of rows over a few years, and a query that was fine on a fresh install becomes a full table scan once the table is large.
Adding or dropping an index rewrites the whole table and briefly locks it on larger tables, so take a backup first, not after something goes wrong mid-ALTER. CloudStick's per-database backup makes this a one-click step, and the Enable Database Backup guide covers turning it on if it isn't already running on the server.
Avoid N+1 Query Patterns
An indexed query that runs once is fast. The same indexed query run 200 times in a loop is still slow, just for a different reason — this is the N+1 pattern, and the slow query log will not flag it directly because each individual query looks fine. You only notice it by seeing the same query shape repeated dozens of times per page load in the log, or by profiling with something like Query Monitor.
The fix is either a single JOIN, as above, or when a JOIN would multiply rows awkwardly, one batched query with WHERE order_id IN (9931, 9932, ...) followed by grouping the results in PHP. Both replace N round trips to MySQL with one, and the win compounds — each round trip pays TCP and query-parsing overhead on top of the actual work, so 40 tiny queries are almost always slower than 1 query doing 40 times the row scanning. This pattern shows up constantly in custom PHP apps that load a list of records and then loop over them to fetch related data one at a time, and in WordPress themes that call get_post_meta() repeatedly inside the_loop instead of priming the meta cache up front with update_post_meta_cache() or a properly batched custom query.
Add a Redis Object Cache Layer
Even after indexes and N+1 fixes, most pages still run the same handful of queries on every single request — the same options lookup, the same menu query, the same "get current user" call. An object cache stores the result of that query in Redis after the first run, so the next 999 requests read it from memory instead of asking MySQL again.
For a custom PHP app, the same idea applies without WordPress-specific plumbing: wrap the query in a check against Redis first, and only hit MySQL on a cache miss, then write the result back to Redis with a sensible TTL. This is exactly what CloudStick provisions Redis on the server for — it sits next to PHP-FPM and MariaDB as an object cache layer, and turning it on for a site removes an entire class of repeated, identical read queries from the database without touching a single line of query logic. Cache invalidation is the part that bites people: make sure writes (an order status change, a settings update) clear the relevant cache key immediately, or you will serve stale data confidently and quickly instead of correct data slowly.
Tune innodb_buffer_pool_size
The InnoDB buffer pool is where MariaDB keeps table and index data in RAM. If your working set of "hot" data doesn't fit inside it, MySQL re-reads the same pages from disk over and over, and every one of those reads is orders of magnitude slower than a memory access — no amount of indexing fixes a buffer pool that's too small.
A useful starting rule of thumb on a server dedicated mostly to the database is 60-70% of total RAM, leaving the rest for PHP-FPM workers, Redis, and the OS page cache. Dividing Innodb_buffer_pool_reads (reads that had to go to disk) by Innodb_buffer_pool_read_requests (all logical reads) gives you a miss ratio — if it's consistently above a few percent on a warmed-up server, the pool is too small for your data. Raising it too high on a shared or memory-constrained server is its own failure mode: MySQL gets starved of memory it needs elsewhere and can crash under load, so change it in a config file, restart MariaDB, and watch memory usage for a day rather than maxing it out blindly.
Clean Up Bloated Tables
A site that's been live for a few years accumulates a specific kind of weight: WordPress wp_options rows marked autoload = 'yes' get loaded into memory on absolutely every page load, and a poorly behaved plugin can quietly grow one of those rows to megabytes. Orphaned wp_postmeta rows left behind by deleted posts, and years of unpruned post revisions, do the same thing to wp_postmeta and wp_posts.
These are destructive statements running against production data — take a per-database backup before you run any DELETE or OPTIMIZE against a live table, so a bad WHERE clause is a five-minute restore instead of a rebuild from scratch. CloudStick's Visual Database Manager lets you inspect table sizes and row counts, and trigger that backup, without opening a terminal at all.
Run the cleanup, then re-run EXPLAIN on the queries that were flagged earlier — smaller tables mean fewer rows scanned even before you touch an index, and OPTIMIZE TABLE rebuilds the table to reclaim the disk space and defragment the indexes after a large DELETE.
Measure Before and After
None of the fixes above are safe to apply on a hunch, and none of them are done once applied — they need a before-and-after number, or you're just guessing with extra steps. Capture the slow query log's top offenders and their EXPLAIN plans before you touch anything, apply one change at a time, and re-check the same queries against the same log afterward.
In practice that order is: turn on the slow query log and let it run through real traffic, EXPLAIN the worst offenders and add the indexes they're missing, find and collapse any N+1 loops the log reveals, put a Redis object cache in front of the queries that repeat identically on every request, size the InnoDB buffer pool to the server's actual RAM, and clean out the autoloaded options and orphaned rows that have been quietly growing every table for years. Once long_query_time stops catching anything meaningful, turn it back up or disable the log entirely — it has done its job, and leaving it at an aggressive threshold indefinitely just adds logging overhead for no benefit. Do it in this order and each step is measurable on its own; do it out of order — say, throwing more buffer pool RAM at a query that's doing a full table scan on a bloated table — and you'll spend money on hardware to paper over a problem a single index would have solved for free.
