A db index is analogous to a table of contents. This helps me understand db index in an easy way. My question is are tehre any real world analogies for a clustered index?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
If the Table of Contents in the front of the book is non-clustered, the page numbering itself (on the actual pages) is the clustered index. The "clustered" nature of an index indicates that the records are stored with the index nodes (or at least in the same order). So, the page-number analogy is actually very accurate in this respect, because like a real clustered index, the page numbers are located on the pages, which are bound together in page number order.
Put another way:
When you use the book's TOC (non-clustered index), you search for a term which points to a page number. You then have to perform another search to locate the actual page.
The search performed against the page number (clustered index) leads you directly to the page. No secondary searching is required.
Not incidentally, this is why you can only have one clustered index. It defines the physical order of the records.
I would describe a clustered index as "the actual order." Thus, for example, a physical dictionary has a clustered index on word, ordered in ascending order from A-Z (slight simplification). This also explains why a clustered index is so much faster to read than a regular index. If I feel the urge to read a book by index entry it will require me to jump all over the book, since consecutive index entries tend to point to different pages. On the other hand, reading every word starting with "The" in a dictionary requires less jumping around; merely find "The", then read in order.
The library (or bookstore if you prefer) puts books into groups (all history books here, scifi books there) because they assume that if you want one history book, you're more likely to want other history books rather than different kinds of books. Likewise, it becomes easier to sort the scifi books by author rather than all books by author.