DEV Community

Discussion on: Explain what is DB indexing like I'm five

bgadrian profile image
Adrian B.G. • Edited on

You like to play with your toys all around the house, and you have a big house. PS: now the parents regret buying all these toys, but is too late.

But how do you find a specific toy when you need it? Lucky for you, all the toys have GPS locators in them.

You have a smart watch (we are in the future of course), and you can talk with it. You can ask it to find your toys only using 2 criterias

  • type (puzzle, car, ball etc)
  • color

You can say to your watch:

  • where is the blue car? The smart watch will respond instantaneous "near the living room TV"

Your watch is smart, he knows where every toy is by using a simple technique, when a toy is moved in the house, he updates it's location.

Every time

  • your parents buy a toy (INSERT) - he adds to its memory with 2 labels (type and color)
  • a toy is repainted (UPDATE) - the "color tag" is updated
  • a toy is trashed (DELETE) - it is removed from the memory

Each toy has an unique identifier (like the serial number from the toy factory) that the smart watch needs to know in order to identify it.
The watch keeps this connections (ID - type, color, position) in it's memory and persistent storage.

smart watch - the database and database index
toys - records in a table database
type and color - columns that have index
searching for a toy - SELECT queries USING at least an index
a toy is moved in the house - the database storage system may move the data stored on the DISK around, or if is shard on multiple servers.

Extra: each toy can have it's own unique nickname, and you can search by it.
That would be an UNIQUE index, means that 1 nickname cannot be used for 2 toys.

hypedvibe_7 profile image
Kate Komar Author

That's gorgeous, thanks

danieljsummers profile image
Daniel J. Summers

Yep - I was going to go back to my kids, and their Hot Wheels/Matchbox car cases where they could see the side of each car. This is better than that. :)

moopet profile image
Ben Sinclair

This doesn't really explain indexing. It's a prerequisite for it, though.

bgadrian profile image
Adrian B.G.

True, it explains more of what is it how is it used more than how it is implemented, but they are 5y old so ....

Like the index, the smart watch keeps a copy of the data (toys characteristics) in a way that is efficiently to search for a specific value.

We could add the there are many types of smart watches, you have to choose it depending on the toys you have and the queries you want to do.