Easy Way to Understand and Work with SQL Indexes

In this guide, we're going to discuss indexes in sequel. I thought quite a bit about the best way to understand indexes because if you've never worked with them before they may seem a little bit foreign. I think this is one of the better ways of thinking about it.

Follow me:

Twitter: https://twitter.com/jordanhudgens
Instagram: https://www.instagram.com/jordanhudgens/
GitHub: http://github.com/jordanhudgens

You could imagine that each one of these items is actually a record in the database. I wanted to kind of strip out the entire idea of showing my sequel workbench and all of the things around it because those things were all distracting when it came to understanding just the basic high-level concept of what an index does will go into what an index actually is right after this.

Let's imagine that we have a ton of records so you can see that we have record after record after record. And let's imagine a scenario where we need to find a specific record. Well, if we do not have an index then what we're essentially going to be doing or what's going to be happening behind the scenes is my sequels going to say "okay, is it this post? No. Is it this one? No. This one? No."

It's going to keep on going down to check every single post and then who knows how long it's going to be until you find the one you want. If you had a scenario where you had five million posts and records in your database you're going to be sifting through a lot of content. And so this is going to be really annoying.

This is the one line I put in there it's called "what I am really looking for." If I were to search through my database for "what I am really looking for" this could be incredibly slow when it comes to finding it. That's what I wanted you to think about was why indexes are even necessary. And it comes down to the fact that an index will allow us to speed up this process because right now we have to look line by line. But what happens if I say I want to see numbers here? So each one of these now has a number by it

Which means it has a reference point you could say it has an index. Now if I go all the way down I can see that this line of code or this post I was looking for is on line 317. What this means for me is when I'm searching for these values I don't need to think of just trying to find where is this one record? I can actually just skip down to the exact spot that I want this makes it much more efficient to be able to find what I'm looking for. I don't have to go line by line because the system essentially already has a mechanism for being able to search through it.

Now the true mechanism behind this gets a little more into some more complex algorithms and data structures and I'll show where you can find what that is. But what it allows the system to do is instead of just trying to go line by line and find it in a linear fashion. It can actually go and skip ahead and ignore a huge chunk of the records that are not applicable just by having this index so you could think of an index essentially as a reference point. In sequel workbench let's go and see our indexes because believe it or not you've actually been using indexes this entire course and many of the indexes are actually created by default. Especially when you use tools like my sequel workbench.

If I click on addresses and then click on the little inspect icon this is going to bring up all of the different data points that we've seen before. But you may notice here we have this indexes tab.

If I click on this it's going to show all of the columns in the table. But it's also going to show all of the indexes that we have in the table. So you can see that by default whenever you create a table and you define a primary key that is going to create an index and right here we have an index of primary key and we know that it is unique and so right here you can see that he even has a name of unique.

Full written guide, screenshots, and code here:
https://www.edutechional.com/2018/08/13/sql-tutorial-improving-query-performance-with-sql-indexes/

Видео Easy Way to Understand and Work with SQL Indexes канала edutechional