In this guide 6 sections
A report times out on a wide range because its cost grows with every row it must read. Usual causes are a missing index, a query that works row by row, and heavy work done while the user waits. Fixes are indexes, summary tables, caching or a background job.
Why one day works and one quarter does not
A single day might touch two thousand rows. A quarter touches ninety times that. If the query is built badly, time does not grow ninety times. A join or a sort can make it climb far faster than the row count.
Most gateways cut a request off after a fixed wait, often thirty or sixty seconds. The report is not broken. It needs longer than the server allows, so the browser shows a gateway timeout.
Time the same report for one day, one week and one month. Plot the three numbers. A straight line means a large but sound query, while a steep curve means something inside it is wrong and will only get worse as the table grows.
Read the query plan before changing anything
Copy the exact query from the slow query log. Run it under EXPLAIN ANALYZE on a test copy. The plan shows whether the database read the whole table or used an index.
Look for a sequential scan on a large table, a sort that spilled to disk, or a nested loop that ran thousands of times against the same child table. Each has a known fix. Guessing wastes a week.
Many reports filter on a date column with no index. Others wrap it in a function such as DATE(created_at), which stops the index from being used at all.
- Sequential scan on the invoices, orders or samples table
- Sort or group using temporary disk files
- Date column filtered inside a function
- A join with no index on the foreign key
Check what the page does while the user waits
Some slowness lives outside the database. The application may load every row into memory and total it in a loop. It may fire one extra query per row to fetch a customer name, the N plus one problem.
Open the profiler and count the queries behind one request. A report of three thousand lines that fires three thousand queries is a design flaw in the code. More hardware will not cure it.
Check formatting work as well. Building a PDF or an Excel file with ten thousand styled cells takes real time. Doing it inside the web request is a classic cause of the timeout.
Choose between summary tables, caching and pagination
Summary tables suit totals asked for again and again, such as sales by day, stock by bin or tests by lab. A nightly job fills a small table. The report then reads only that table and returns in a blink.
Caching suits reports whose answer cannot change once the period has closed. Last month’s figures can be stored the first time they are built. Pagination suits long detail lists, since nobody reads row nine thousand.
Each choice has a cost. A summary table can drift from the source after a late edit. Decide how stale a figure may be, and print the build time on the report.
- Summary table refreshed each night for daily totals
- Cached result for closed months, cleared on a late edit
- Pagination or an export link for detail lines
- A limit on the range, with a message that explains it
Move heavy reports to a background job
When a report must be exact and wide, stop making the user wait at the screen. Let them press a button, place the request in a queue and carry on with other work until the file is ready.
The page shows a status: queued, running or ready. An email carries the link once the file is built. A worker process does the heavy reading, away from the screens other staff are using.
Schedule it for off hours where you can. Then ask someone to test the widest range your accounts team will realistically request.
Slow report worksheet
One page with a line for each slow report. Columns: report name, timings for one day, one week and one month, and the slow query text. Then the plan finding, the chosen fix (index, summary table, cache, pagination or background job), the owner and the retest result.
Open a blank worksheet to printQuestions owners ask
Will a bigger server fix a report timeout?
Rarely. If the query reads the whole table, a faster disk only reads it a little faster. An index or a summary table removes the work itself, which a larger machine cannot do.
Is it safe to add an index to a live table?
Usually, but a big table needs locks and disk space. Try it on a copy first. Then add it at a quiet hour using the online option your database offers.
Why does the report get slower every month?
The table grows and the query reads more of it each time. Archiving old rows, or summarising them, keeps the working set small while the business keeps adding records.