Database indexing and query crafting is more of an art than a science. There are a lot of rules of thumb we often follow that work the majority of the time, but not always.
Need all the users created this year? No problem! Slap an index over created_at and use a simple SELECT with a WHERE and you’re all set! Now just active users? We’ll just stick status on the end of that index and add an AND to that query! What about active OR pending users since January 1st, sorted by username? Are we still covered index-wise? Is our query even using that index? Is it as fast with twenty million users?
We’ll start off with some simple queries and ideal indexes for them based on some general “rules” lots of us use. Then we’ll ramp up and introduce some new, real world factors like larger table size, different data types, and increased traffic.
With those in mind looking at a few fairly more involved queries, you’ll learn how to identify where those best practices fall down, why, and how to find the best optimization.
We’ll have fun breaking the “rules” and writing new ones that work for us.
