5 ms·

I am not a database expert but as a developer, I of course work with databases and SQL as needed. My answer would be: "Index helps you locate information quick

I am not a database expert but as a developer, I of course work with databases and SQL as needed. My answer would be: "Index helps you locate information quicker than usual in a database. If database is similar to a Book, then think of index as a Table Of Contents with page numbers for specific topics. Imaging if I gave you a book with 1000 pages and ask you to find a specific topic but there is no table of contents. You will have to look through many pages before you find it. If it had a table of contents, you could flip to that page much faster. Will this answer be good enough for you ? But then, I have no technical knowledge of how index is implemented within various databases and that is where I will say "Sorry I can't tell you the technical details". EDIT: I will also add that it is difficult to list EVERY possible topic in a book in the table of contents. It will then defeat the purpose of the TOC. So you have to pick and choose the topics that you want to show with page numbers. Same for database index. You cannot index everything.

SomeStupidPoint 9y ago
Isn't an index sort of like an index? It gives you mappings that don't align with the primary ordering (ToC/page num).
JshWright 9y ago
I would expand on that a analogy a bit, as a book index (like a database index) is not without cost. It takes up space in the book that now can't be used for the actual text, it may not be useful for certain types of searches (i.e. the book index lists topics by title, but you're trying to find them by author, etc), and you have to keep the index up to date with later editions of the book (a topic inserted in the middle means you have to adjust the page numbers for a bunch of topics), etc. All in all though, I'd personally be pretty happy with that answer (I'm not the OP, though...)
dragonwriter 9y ago
> If database is similar to a Book, then think of index as a Table Of Contents which page numbers for specific topics. FWIW, books frequently also have indexes, which are a better analog to DB indexes than tables of contents are.
- codegeek 9y ago
  good point :).
dixie_land 9y ago
in my book that'd be a bad answer. thats just a lot of words for "no I don't know". I'd rather being told "no I don't know, but I'm willing to find out if needed"
- sanderjd 9y ago
  This is why we can't have nice things :-) You seem to be looking for technical details, where I'm looking for a mental model that displays understanding. The parent's post is "this is how it works", and you want "this is how it is implemented". Someone interviewing has no idea which of the two of us they are talking to. That's why the technical interview process is so fraught.
  - dixie_land 9y ago
    I think the context is important. My comment was assuming a technical interview. It is implied in this context "how" means how it was implemented rather than how to use it. Otherwise the question would be phrased more explicitly "how to use db indexes to (optimize performance, etc.) For instance if I were an auto repair shop owner asking a technician candidate "do you know how a small block ford work" I meant how the manifold, heads, rotary etc. fit together rather than how to drive a kick ass street rod (which is cool but not the skill I'm interviewing you for)
    - sanderjd 9y ago
      To continue your car analogy, I think the question about indexes is more like asking "how does a piston work?". For me, it would be more important to display a strong mental model of the role a piston plays in the overall function of the engine and how it does its work in general than to know technical details about different real pistons in different real engines. It seems like you feel the opposite, which is also valid, just different. My point was really that a major reason interviews are so daunting is that candidates don't know whether they're talking to someone like you or someone like me.
      - 9y ago
sanderjd 9y ago
It's interesting that you chose a book-world table of contents as the analogy for a computer-world index, instead of choosing the book-world index that (I believe) the computer-world index takes its name from :-). I think your answer is a great one, but that an important thing to point out (which also fits into the analogy) is that an index is a trade off between lookup efficiency vs. storage size (the more words included in a book's index, the more pages it requires) and write efficiency (every time you write a word that is included in the index, you need to go update that word's entry). Understanding the point of indexes and their implicit trade-offs seems like fair game to me. This sort of read / write / storage size trade-off is relevant to basically all software. Having a sense for the different kinds of indexes and how they are implemented seems like bonus.
bjourne 9y ago
That's a good answer! The question is a good interview question too.
mountaineer 9y ago
This would be really good answer in my view and could go in several different directions to talk about performance and trade-offs. I'm much more interested in that, than in going into B-tree details.

SomeStupidPoint 9y ago

Isn't an index sort of like an index? It gives you mappings that don't align with the primary ordering (ToC/page num).

JshWright 9y ago

I would expand on that a analogy a bit, as a book index (like a database index) is not without cost. It takes up space in the book that now can't be used for the actual text, it may not be useful for certain types of searches (i.e. the book index lists topics by title, but you're trying to find them by author, etc), and you have to keep the index up to date with later editions of the book (a topic inserted in the middle means you have to adjust the page numbers for a bunch of topics), etc. All in all though, I'd personally be pretty happy with that answer (I'm not the OP, though...)

dragonwriter 9y ago

> If database is similar to a Book, then think of index as a Table Of Contents which page numbers for specific topics. FWIW, books frequently also have indexes, which are a better analog to DB indexes than tables of contents are.

codegeek 9y ago
good point :).

dixie_land 9y ago

in my book that'd be a bad answer. thats just a lot of words for "no I don't know". I'd rather being told "no I don't know, but I'm willing to find out if needed"

sanderjd 9y ago
This is why we can't have nice things :-) You seem to be looking for technical details, where I'm looking for a mental model that displays understanding. The parent's post is "this is how it works", and you want "this is how it is implemented". Someone interviewing has no idea which of the two of us they are talking to. That's why the technical interview process is so fraught.
- dixie_land 9y ago
  I think the context is important. My comment was assuming a technical interview. It is implied in this context "how" means how it was implemented rather than how to use it. Otherwise the question would be phrased more explicitly "how to use db indexes to (optimize performance, etc.) For instance if I were an auto repair shop owner asking a technician candidate "do you know how a small block ford work" I meant how the manifold, heads, rotary etc. fit together rather than how to drive a kick ass street rod (which is cool but not the skill I'm interviewing you for)
  - sanderjd 9y ago
    To continue your car analogy, I think the question about indexes is more like asking "how does a piston work?". For me, it would be more important to display a strong mental model of the role a piston plays in the overall function of the engine and how it does its work in general than to know technical details about different real pistons in different real engines. It seems like you feel the opposite, which is also valid, just different. My point was really that a major reason interviews are so daunting is that candidates don't know whether they're talking to someone like you or someone like me.
    - 9y ago

sanderjd 9y ago

It's interesting that you chose a book-world table of contents as the analogy for a computer-world index, instead of choosing the book-world index that (I believe) the computer-world index takes its name from :-). I think your answer is a great one, but that an important thing to point out (which also fits into the analogy) is that an index is a trade off between lookup efficiency vs. storage size (the more words included in a book's index, the more pages it requires) and write efficiency (every time you write a word that is included in the index, you need to go update that word's entry). Understanding the point of indexes and their implicit trade-offs seems like fair game to me. This sort of read / write / storage size trade-off is relevant to basically all software. Having a sense for the different kinds of indexes and how they are implemented seems like bonus.

bjourne 9y ago

That's a good answer! The question is a good interview question too.

mountaineer 9y ago

This would be really good answer in my view and could go in several different directions to talk about performance and trade-offs. I'm much more interested in that, than in going into B-tree details.