The Secret Life of SQL: How to optimize Database Performance

There are a lot of database index and query best practices that sometimes aren't best practices at all. Need all users created this year? No problem! Slap an index over created_at! What about this year's active OR pending users, sorted by username? Are we still covered index-wise? Is the query as fast with 20 million users? Common rules of thumb for indexing and query crafting aren’t black and white. We'll discuss how to track down these exceptional cases and walk through some real examples. You'll leave so well equipped to improve performance, you won't be able to optimize fast enough!

50 Shades of Database Performance Optimization

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.