It was three in the morning on one of those panic-filled Tuesdays when the client's server simply decided to stop responding because of a poorly written report. We always think the database will handle everything on its own and that new hardware will solve last week's problem, but in my experience the truth is that almost always the code itself is a mess. When the page just sits there loading forever and the API starts timing out, the team immediately starts talking about scaling the machine or switching databases, but honestly the issue is usually much simpler and way dumber. It all starts when someone shoves a SELECT with an asterisk into a massive table without thinking about what they are doing. It seems like a minor thing when the application is fresh, when you have ten users in the system and everything runs fast, but then the table keeps growing and suddenly the database is trying to pull thirty columns out of memory just to deliver two that were the only ones the page actually needed to show on the grid. Which actually reminds me of that obsession with throwing UPPER or date functions right in the middle of a WHERE clause. People love writing things like WHERE YEAR of the sale date equals 2026 because it looks cleaner to read in the code, but the database engine looks at that and cries. It has to take every single one of those five million rows, apply the function one by one, and only then compare them, completely destroying any chance of using an index. If you rewrite that to use a simple range with the date greater than or equal to the start of the year and less than the following year, the query drops from four seconds to a handful of milliseconds almost like magic. ```sql -- What most people write without thinking SELECT * FROM sales WHERE YEAR(sale_date) = 2026; -- How it should be written to take advantage of indexes SELECT id, amount FROM sales WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'; ``` Nobody likes looking at the EXPLAIN ANALYZE command in PostgreSQL or MySQL because that text tree looks confusing and annoying to read in the terminal. But that's where the whole truth of what's happening behind the scenes lives. If you see the expression Seq Scan in the report, it basically means the database had to sweep the entire disk page by page, which on a system under load at noon is going to blow up the whole machine. But wait, going back to that thing about functions in the WHERE clause I was talking about a moment ago, that applies to text too when people throw a percentage sign at the beginning of a LIKE. A LIKE with a percentage at the start ignores the B-Tree index structure just the same, and the database is forced to read the whole table all over again. Another thing that drives me crazy is pagination using OFFSET. Early in the project when there are only three pages of data everything works fine, but when the user gets to page five hundred and the code runs OFFSET 100000, the database needs to read a hundred thousand rows off the disk, throw those hundred thousand away, and deliver only the next twenty. It's an absurd waste of resources that you can fix by doing cursor-based pagination, saving the last ID seen and doing a simple WHERE id is greater than the last one. ```sql -- Inefficient for pages way down the line SELECT id, title FROM articles ORDER BY id LIMIT 20 OFFSET 100000; -- Direct lookup using a pointer SELECT id, title FROM articles WHERE id > 100000 ORDER BY id LIMIT 20; ``` Then people remember indexes exist and decide to create an index for every single column in the table thinking it will save the world. Except indexes have a high invisible cost on INSERTs and UPDATEs, not to mention taking up disk space. If you're doing frequent searches using the order status and the customer ID together, create a composite index with those two columns together instead of creating two separate indexes that will only slow down writes. ```sql -- Example of an old query that used to freeze the system SELECT * FROM orders WHERE UPPER(status) = 'COMPLETED' AND customer_id IN (SELECT id FROM customers WHERE country = 'Angola') ORDER BY created_at DESC LIMIT 50; -- The same query refactored and ready to fly SELECT o.id, o.total_amount, o.created_at FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE o.status = 'completed' AND c.country = 'Angola' AND o.created_at >= '2026-01-01' ORDER BY o.created_at DESC LIMIT 50; ``` For that case in the query up there, creating a specific index made all the difference in response time on the production server. ```sql CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC); ``` Replacing the subquery with an explicit JOIN and standardizing text formatting on insert so you don't need UPPER cleared up almost the entire execution plan. I think deep down we spend hours trying to guess memory configurations in the server config file when ten minutes spent looking at the code and tuning the queries would fix the issue for good, or at least let you sleep peacefully without your phone ringing off the hook.