Week #1: 100 Days of SQL Optimisation
How Small Tweaks Transformed Our Queries, Saving Time and Resources
Think of each SQL query like a high-stakes puzzle. At first, you might try every piece, hoping something fits. But with the right strategies, indexing, filtering, and window functions, you solve it faster, with everything snapping perfectly into place.
The data used in the examples below comes from IMDb's Non-Commercial Datasets1, which offer a wealth of information about movies, ratings, and metadata. These datasets are ideal for practising SQL, as they include real-world data that presents various challenges for efficient querying.
In this post, we dive into week one of our 100-day SQL optimization journey.
Day 1: Basic Data Retrieval Optimisation
Goal: Retrieve the top 100 movies by rating from the title_basics and title_ratings tables.
Details:
Day 2: Multicolumn Indexes in Filtering
Goal: Retrieve movies released between 2010 and 2020 with an average rating above 7 and at least 1000 votes.
Details:
Day 3: Filtering Before and After Aggregation
Goal: Calculate the average rating for movies in specific genres (Drama and Comedy).
Details:
