Backend Engineering (Intermediate)

Currently Playing: Watch out before Adding Indexes to Your Table, Your Database Optimizer Might not Use them

In this video we will take a look at how Database Optimizer uses multiple indexes based on the query, when does it use an index over the other. This is very useful if you want to evaluate whether it's worth it to add an index or not. This video will not go through the combined indexes which I have already discussed previously. * Intro 0:00 * Planner uses Multiple Indexes 1:00 * Read multiple indexes and merge results (postgres bitmap index, oracle merge) * Planner uses One Index + Filter 3:00 * Read one index only and filter based on the condition after going to the table 3:00 * e.g. one of them is a primary key * one of the indexes stats will return a much lower row count * Planner uses no indexes * Full table scan if we know indexes are too slow to traverse or we know we will return so much result * Statistics can bite you! 6:00 * How Hints can override the optimizer 8:00 * hints, hey optimizer don’t try to be smart, your stats suck I know better use this index. * When to use Indexes 9:00 🎙️Listen to the Backend Engineering Podcast https://husseinnasser.com/podcast 🏭 Backend Engineering Videos https://backend.husseinnasser.com 💾 Database Engineering Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2 🏰 Load Balancing and Proxies Videos https://www.youtube.com/playlist?list=PLQnljOFTspQVMeBmWI2AhxULWEeo7AaMC 🏛️ Software Archtiecture Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXNP6mQchJVP3S-3oKGEuw9 📩 Messaging Systems https://www.youtube.com/playlist?list=PLQnljOFTspQVcumYRWE2w9kVxxIXy_AMo Become a Member https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join Support me on PayPal https://bit.ly/33ENps4 Stay Awesome, Hussein