Every optimization technique in this course so far assumes you already know which query is slow. In a real site with hundreds of queries running per page across core and a dozen contributed modules, that's rarely obvious just by reading code — you need to actually watch what the database is doing. Drupal gives you two practical ways to do that: the Devel module's query log, and your database server's own slow-query log.
What you'll learn in this lesson
- How to see every query a page ran, and how long each one took, using the Devel module
- How to enable your database's slow-query log for a production-safe, ongoing view
- What to actually look for once you have the data
The Devel module's per-request query log
With the contributed Devel module enabled, its toolbar shows a query count and total query time for the current page, and clicking through lists every individual query, its execution time, and the calling function. This is the fastest way to answer "why is this specific page slow" during development — you get an ordered list, slowest first, with enough context to know exactly which line of code issued each one.
Your database's own slow-query log
-- MySQL/MariaDB: log any query taking longer than 1 second
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';Unlike Devel, this runs safely on production — it's a standard database server feature, not something Drupal adds, and its overhead is minimal when the threshold is set sensibly (1 second is a reasonable starting point; tighten it once you've cleared the obvious offenders). This log captures the queries that are actually slow under real production data and real production traffic patterns, which local development can never fully replicate.
What to actually look for
Once you have a list of slow queries, prioritize by frequency × duration, not duration alone. A query that runs once and takes 2 seconds is worth fixing eventually; a query that runs on every single page load and takes 50ms is often a bigger overall drag on the site, because that 50ms is paid by every visitor, every time. Use EXPLAIN (mentioned in the indexing lesson) on the worst offenders to see whether they're missing an index, doing an unnecessary join, or pulling far more rows than needed.
Quick check: query A takes 3 seconds but runs once a day during a scheduled report; query B takes 80ms but runs on every single page view across a high-traffic site. Which should you optimize first? (Query B, almost always — its total cost across a day of traffic vastly exceeds query A's, even though A looks scarier in isolation. This is exactly why frequency matters as much as per-query duration.)
Key takeaways
- Use Devel's query log during development to see exactly which queries a specific page ran and how long each took.
- Enable your database's slow-query log on staging/production for real, ongoing visibility that development data can't replicate.
- Prioritize fixes by frequency × duration, not raw duration — a cheap-but-constant query often costs more overall than a rare-but-slow one.
- Follow up a slow query with
EXPLAINto find the actual cause (missing index, unnecessary join, too many rows) before rewriting it blindly.
Coming up next
Database queries are only half the performance picture — the browser has to download, parse, and render everything the server sends back too. Next, we move to the frontend and start with aggregating CSS files to cut down on the number of requests a page needs.