Slow Database Queries
What Makes a Query Slow
Slow database queries are requests that take noticeably longer to return results than they should. A query is any instruction sent to the database to read or change data. Each one takes time for the database to find the matching rows, perform any joins or calculations, and send the results back.
Most slow queries come down to the database doing more work than necessary. Common causes include missing indexes, full table scans, very large tables and locking. Slow queries also matter beyond the page itself, because a query that holds on to a connection can contribute to connection limits being reached.
Common Causes of Slow Queries
Without a suitable index, the database may perform a full table scan, reading every row to find the few it needs. That is harmless on a table of a hundred rows and painful on a table of millions. An index gives the database a shortcut to the rows it needs.
Other causes include selecting far more columns or rows than a page needs, joins that match large sets of rows, and subqueries that run again for every row. Locking is another: when one transaction holds a lock on rows, other queries wait for it to finish. Application code can add to the problem when an ORM sends many small queries in a loop.
Finding Slow Queries: The Slow Query Log and EXPLAIN
The slow query log records queries that exceed a set time threshold, so you can see which statements cause the most delay. It is the usual starting point, because it shows real queries from real traffic rather than guesses.
EXPLAIN is a command offered by most database systems. It shows the database’s plan for a query: which tables it reads, whether it uses an index and roughly how many rows it expects to examine. Comparing that plan with what you expected usually reveals the problem.
A Product Search That Slows Down
A product search page works well with a few hundred items. After the catalogue grows to a hundred thousand products, the search takes several seconds. The slow query log shows a search filtering on a column with no index, so the database checks every product row each time. An index on that column lets the database jump straight to matching rows. The improvement comes from one measured change, not from guessing.
Frequently asked questions
Will adding indexes always speed up slow queries?
Not always. Indexes help reads on the columns they cover, but each index adds storage and makes inserts and updates a little slower, because the index must be maintained. Too many indexes can slow writes noticeably. Adding them based on measured slow queries is better than adding them speculatively.
Can a slow website be caused by the database?
Yes, though not always. Slow page loads can also come from the web server, application code, external API calls or large images. Checking the slow query log and how much time pages spend waiting on database calls shows whether the database is the bottleneck.