Leverage SQL indexing to speed up your queries. Learn when to index, when not to, and how indexing works under the hood.
SQL indexing is a term often thrown around in data circles — you may have heard phrases like “just apply an index”. It is also a question often asked in interviews — “what steps can take to improve query times on table X?”. It is something that is syntactically easy to implement but I have found not much attention is paid to what actually happens under the hood. In this article I aim to do just that by using a relational MySQL Database (DB). I will cover what an index is, how to implement it, how it works under the hood, along with some considerations of when not to use indexes. As with many technologies, even SQL indexes have their trade-offs.
In my examples I use a simple MySQL container from Docker. I do not cover how this works but feel free to reach out if you have any questions. I will show the code I use to populate the DB in this article for you to adapt for your own use case and experiment yourself.
I start off with a high-level overview. The more granular detail is later on in the article. As such, I hope I can provide valuable insights to a wide readership of varying technical inclinations. If you’re like me you’ll find the visualisations in…