How to explain indexes

database-designdatabase-theoryindex

This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database.

Books? What, on paper? Index cards in drawers?

The youngsters don't really know what I'm talking about, so how do we best explain it nowadays?

(Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)

Best Answer

Best is to refer : Stairway to SQL Server Indexes

You leave your house to run a few errands. When you return, you find a message from your daughter’s softball coach waiting for you. Three of the girls, Tracy, Rebecca, and Amy have lost their team caps. Could you please swing by the Athletic Products Store and buy caps for the girls. Their parents will reimburse you at the next game.

You know the girls and you know their parents. But you do not know their hat sizes. Somewhere in your town are three residences, each containing a piece of information that you need. No problem, you’ll just call the parents and get the hat sizes. You reach for your phone, and you reach for an index – the white pages of your telephone directory.

The first residence that you need to reach is that of Helen Meyer. Estimating that “Meyer” will be located near the middle of the population, you jump to the middle of the white pages; only to discover that you are at the page whose heading says “Kline-Koerber”. You make a smaller jump forward and reach the “Nagle-Nyeong” page. One even smaller jump backwards puts you at the “Maldonado-Nagle” page. Realizing that you are now at the correct page, you scan down the page till you reach the “Meyer, Helen” line and obtain the telephone number. Using the phone number, you reach the Meyer residence and obtain the information you need.

You repeat the process two more times, reach two other residences, and obtain two more hat sizes.

You have just used an index, and you have used it in much the same way that SQL Server uses an index.

For more reading, you can refer to

Related Question