This One SQL Clause Made My Query 10x Faster

Non Medium members read from here for free.

It began with a bottleneck — and a headache.

It was a Thursday afternoon, and I was deep in performance reports for a fintech client. Their user base had exploded in Q1, which meant our once-snappy dashboards were now moving at the pace of a dial-up modem.

I was tasked with optimizing a key query that powered their customer spend analysis. Nothing too fancy — just pulling total transaction amounts by customer segment for the past quarter.

But here’s the catch: the transactions table had over 150 million rows.

Photo by on

And my query? It was taking over 2 minutes to run. Sometimes even timing out.

The original query (aka “why is this so slow”)

Here’s what I started with:

SELECT
    c.customer_id,
    c.segment,
    SUM(t.amount) AS total_spent
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.transaction_date >= '2024-01-01'
GROUP BY c.customer_id, c.segment;

At first glance, it seems clean. Indexed foreign key join. A WHERE clause to filter recent transactions. Group by to get spend by customer.