Database Indexes

One of the most important concepts in database design is Indexes. But don't I mean Indicies? No. Indexes. That's how I roll.

Fundamentally, Indexes are like pointers to locations. To make things findable fast, in any situation, it helps to be able to point to the exact location you need to go to get whare you are looking for. But thinking at the computer memory level is harder to wrap our heads around than real life, so think of it like a hotel room.

We are to meet at The Kingson Hilton at room 505 right now. Could you find me? Probably pretty easily. If I tell you we need to connect, but I only tell you that I am in the Kingson Hilton, and you had to knock on every room, then it would take a long time to find me. If there were 1000 rooms, you would have to knock on upto 1000 doors to find me. Not very efficient right?

But for every hotel room you want to point to, you need some information about the existence of the room and who is in there. This is exactly what Indexes are. If you show up at the hotel, the front desk would be able to look at their Index and see where you are.

Thus, saving you from 999 doors you might otherwise need to knock on to find me.

The problem gets slightly worse in databases. I may represent a value, like a year of birth. And I could exist in many rooms. To find all these rooms, you would need to consult the Index again. If it is not in the Index, I have to ask every room.

So an Index is essentailly some way to find something super-fast in the database. If you are going to need to find results fast, you need to index based on that.

So for example, I don't need to index everybody's first name. Unless I need to know how many Jeffrey's there are. Then it makes sense. But if I only ever need to know how many Jeffrey's there are one single time, then it doesn't really make sense because adding the Index affects just as many rows in the database as looking for all the Jeffrey's. But if I am going to be monitoring the Jeffrey levels daily, weekly, monthly or yearly, then it is probably a good idea to add an Index to first name.

Posted In:

jsturgis