SQL — Understand how indices work under the hood to speed up your queries

No more waiting for slow queries to finish

How can we quickly find our data in these rows of records? (Image by Fabien Barral on Unsplash)

Ever wondered how a database engine can return data for your queries that fast? How it can search through many tables, and millions of records in a flash? This article explores how the database engine works under the hood and sheds light on how to design your tables and indices in the most optimal way. No more waiting for queries to finish!

As usual we’ll first set up an example, creating some sample data so we have something to work with. Then we’ll check out how the database engine performs without indices. Then we’ll add indices to speed up our queries, demonstrating how you can too. At the end of this article you’ll:

• understand what an index is
• understand the types of indices and their differences
• understand how the indices work
• know in which situations to use which type of index

Note that in this article we’re using SQL Server, but the principle apply to many other relational databases like Postgres and MySQL e.g. The syntax might differ though. Let code!

Setup

For illustrating the code in this article I’ve created a table that a lot of…