Find and fix slow database queries
A slow site with a busy database usually comes down to a handful of bad queries. Find them, understand them, fix them.
1. Catch the slow ones
MySQL/MariaDB — see what’s running right now:
SHOW FULL PROCESSLIST;Long-running queries show a high Time. You can also enable the slow query
log to record anything over N seconds.
PostgreSQL — the pg_stat_statements extension aggregates the most expensive
queries over time; pg_stat_activity shows what’s running now.
2. Understand why
Run the query with EXPLAIN (MySQL) / EXPLAIN ANALYZE (Postgres). Look for:
- Full table scans → missing index.
- Huge row counts examined to return a few rows → bad filter or no index.
- Sorting large result sets → index the
ORDER BYcolumns or reduce the set.
3. The common fixes
| Symptom | Fix |
|---|---|
WHERE on an unindexed column | add an index |
SELECT * pulling huge rows | select only the columns you need |
| N+1 queries (one per loop item) | eager-load / join instead |
| Same query every request | cache the result in Redis |
Giant OFFSET pagination | paginate by indexed key, not large offsets |
4. Verify
Re-run EXPLAIN and time the query again. Confirm the app page that used it is now
fast. If the whole server is struggling, also check
high CPU/memory.
Prevent regressions
- Review slow queries periodically, not just when things break.
- Add indexes in versioned migrations so they ship with the code.
- Keep an eye on table growth — a query that’s fine at 10k rows can crawl at 10M.
Running OrbixPanel? Local DB access + phpMyAdmin make profiling painless. OrbixPanel.com → — the free, self-hosted, AI-powered hosting control panel.