Week #4: 100 Days of SQL Optimisation
How to optimise SQL queries for maximum efficiency
Think of SQL optimisation as a chess game. A hasty move might capture a piece, but it can leave your position vulnerable. By strategically planning your joins, filters, and indexes, you control the board, anticipating the database’s next moves and achieving checkmate in fewer steps. A well-optimised query is like a perfectly executed strategy, efficient, and precise.♟️♛
This week, we mixed it up by tackling a blend of popular interview questions, and real-world datasets. Each example highlights different techniques for achieving better performance in diverse contexts.
Here’s a list of what you’ll find in this week’s optimisation journey:
Day 22: Solved the Find Followers Count SQL problem from LeetCode1 using randomly generated 100k entries.
Day 23: Optimising a query using randomly generated 1M entries.
Day 24: Solved the Page With No Likes Facebook SQL interview question from DataLemur2, with 100k randomly generated entries.
Day 25: Optimising a query using the IMDb's Non-Commercial Datasets3.
Day 26: Solved the Students and Examinations SQL question from LeetCode4, with 1M randomly generated entry.
Day 27: Solved the Unfinished Parts LinkedIn SQL interview question from DataLemur5, using 5M randomly generated entries.
Day 28: Optimising a query using the Electric Vehicle Population Data by DATA.GOV6.
In this post, we dive into week four of our 100-day SQL optimisation journey.
Day 22: Full Table Scan vs. Indexed Aggregation
Goal: Find the number of followers for each user.
Details:
Day 23: Subqueries vs. Window Functions
Goal: Calculate a cumulative total of sales prices over time.
Details:
Day 24: Left Join with HAVING vs. Filtering with IS NULL
Goal: Return the IDs of the pages that have zero likes.
Details:
Day 25: Effect of Unnecessary Aggregation Functions
Goal: Retrieve a list of movies with their average ratings, ordered from highest to lowest.
Details:
Day 26: Join with/without Aggregating Early
Goal: Find the number of times each student attended each exam.
Details:
Day 27: Partial Indexing
Goal: Determine which parts have begun the assembly process but are not yet finished.
Details:
Day 28: Subquery Filtering vs. Window Function
Goal: Find the top 3 electric vehicle types by country.
Details:
Conclusion
In this fourth week of our "100 Days of SQL Optimisation" journey, we explored several powerful optimisation techniques to improve query performance:
Indexing for Filtering and Aggregation: Used partial indexing and filtering to avoid full table scans and reduce computation times.
Window Functions for Ranking: Replaced complex subqueries with window functions like
RANK()to improve the efficiency of ranking operations.Pre-aggregation with CTEs: Leveraged Common Table Expressions (CTEs) to aggregate data before joining, simplifying join operations.
Avoiding Unnecessary Aggregation: Eliminated redundant aggregation operations to streamline data retrieval and enhance performance.
Efficient Join Techniques: Applied optimised join strategies, such as avoiding late aggregation and reducing redundant joins.
These optimisations significantly reduced execution times, making our queries faster, cleaner, and more maintainable.
If you're interested in learning more practical SQL optimisations, follow along with our 100-day journey. Subscribe to stay updated, each day, you'll gain new insights into making your SQL queries more powerful and efficient!
If you missed the Week 3 recap, you can catch up by checking it out here:
If you're interested in understanding the logic behind these queries, check out our post about "How SQL Queries Executed?"
SQL Behind the Curtain: How Are Queries Executed?
https://leetcode.com/problems/find-followers-count/
https://datalemur.com/questions/sql-page-with-no-likes
https://developer.imdb.com/non-commercial-datasets/
https://leetcode.com/problems/students-and-examinations/
https://datalemur.com/questions/tesla-unfinished-parts
https://catalog.data.gov/dataset/electric-vehicle-population-data