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.
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. :)
I've been a professional C, Perl, PHP and Python developer.
I'm an ex-sysadmin from the late 20th century.
These days I do more Javascript and CSS and whatnot, and promote UX and accessibility.
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
You can say to your watch:
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
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.
That's gorgeous, thanks
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. :)
This doesn't really explain indexing. It's a prerequisite for it, though.
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.