How I Optimised Database Query Time by 80% with Smart Indexing?

Ever had users complain about slow-loading dashboards? I just solved this exact problem for my client’s web app

Our customer dashboard was taking 15+ seconds to load.

After investigation, I found a complex SQL query retrieving user activity data with NO proper indexing.

EXPLAIN SELECT users.name, orders.order_date, orders.total_amount 
FROM orders JOIN users ON orders.user_id = users.id
WHERE orders.status = ‘completed’ 
AND orders.order_date > ‘2024–01–01'
ORDER BY orders.order_date DESC;

So, I implemented strategic indexing:

• Created a composite index on status + order date (DESC)

• Added an index for the JOIN operation on user_id

 –– Composite index for the WHERE conditions and sorting

CREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);

 -– Index for the JOIN operation

CREATE INDEX idx_orders_user_id ON orders (user_id);

With this, Query execution time dropped from 8 seconds to just 1.6 seconds – an 80% improvement with ZERO code changes!

Key Takeaways:

• Understand your data access patterns

• - Order columns in composite indexes strategically (ref. https://stackoverflow.com/questions/795031/how-do-composite-indexes-work)

• Always index foreign keys used in JOINs

• Include frequently sorted columns in your indexes

Sometimes the most impactful solutions are the simplest ones. This small change dramatically improved our UX without requiring additional hardware or complex code refactoring.