Yep, working with indexes is a very important topic. They can downright make or break the performance of a database! I think it's a good idea to rely on a lot of diagrams, and perhaps animated GIFs explaining how the rows are inserted into the index.
Also I find the library metaphor very useful to build a mental model:
Say you're in a library and you're looking for a book. Without any hints, you'll have to search through all the books to find it. But if books are put on shelves in alphabetical order or by genre, that's already much easier! So, put that information in a book at the entrance and voilà — you've got an index!
I'm tossing up as to whether I explain how a B-tree works or just stick to a high-level explanation. That will dictate the strategy I take with gifs, images and which direction I take it.
I also enjoy the library metaphor:
"Databases are like libraries.
Tables are like books stored in a library.
Rows are like pages of a book and to make getting to the page you need quicker, you need to create an index. Flipping through a textbook page by page looking for that one page you need is going to take time, the same way querying millions of rows in an unindexed table is going be time-consuming and tedious."
I think a high-level B-tree explanation would be interesting — but perhaps you could warn that this needs some CS fundamentals (trees)?
Your approach to the library metaphor is interesting — I actually thought of it one level up, i.e. "a table is like a library" because I was thinking about a book as a row in a table. For example, a book has many properties (like genre, publish date, content, title, authors…) that we can relate to columns and relationships. Also, I'd be cautious not to cause confusion because "page" also have a specific meaning in the realm of databases (grouping of rows stored on disk). :-)
This is why I’ve held off tackling indexes so far. I’d like to pitch it to beginners but feel like I’m going to get too far into CS topics I wasn’t intending to explain.
Yep, working with indexes is a very important topic. They can downright make or break the performance of a database! I think it's a good idea to rely on a lot of diagrams, and perhaps animated GIFs explaining how the rows are inserted into the index.
Also I find the library metaphor very useful to build a mental model:
Say you're in a library and you're looking for a book. Without any hints, you'll have to search through all the books to find it. But if books are put on shelves in alphabetical order or by genre, that's already much easier! So, put that information in a book at the entrance and voilà — you've got an index!
I'm tossing up as to whether I explain how a B-tree works or just stick to a high-level explanation. That will dictate the strategy I take with gifs, images and which direction I take it.
I also enjoy the library metaphor:
"Databases are like libraries.
Tables are like books stored in a library.
Rows are like pages of a book and to make getting to the page you need quicker, you need to create an index. Flipping through a textbook page by page looking for that one page you need is going to take time, the same way querying millions of rows in an unindexed table is going be time-consuming and tedious."
I think a high-level B-tree explanation would be interesting — but perhaps you could warn that this needs some CS fundamentals (trees)?
Your approach to the library metaphor is interesting — I actually thought of it one level up, i.e. "a table is like a library" because I was thinking about a book as a row in a table. For example, a book has many properties (like genre, publish date, content, title, authors…) that we can relate to columns and relationships. Also, I'd be cautious not to cause confusion because "page" also have a specific meaning in the realm of databases (grouping of rows stored on disk). :-)
Anyway, excited to read this next article!
This is why I’ve held off tackling indexes so far. I’d like to pitch it to beginners but feel like I’m going to get too far into CS topics I wasn’t intending to explain.
Back to the draft :)
My take on indexes is now up if you’re interested dev.to/helenanders26/sql-series-sp...