What is the purpose of non unique indexes in a database?

Question

I've searched around but I can't seem to find an appropriate explaination behind the concept of unique and non unique indexes in a database.

In Rails, for example, you can create unique and non unique indexes for a given field, as explained at http://railsguides.net/advanced-rails-model-generators/

What I don't understand is: if the purpose of an index is to "set a shortcut" to a value position in a table for faster accessing it, then how could multiple values share the same index?

Say for example I'm storing emails in a table, and I want to index their values positions. If so far I get it right, in case I have non unique indexes, then the DB could have [email protected] indexed at position 150 and [email protected] also indexed at position 150. So if I end up having say 100 different values at position 150, doesn't this defeat the purpose of indexing in the first place if the DB would still have to search through all the values at 150 to find the exact record I need?

How does this make sense ??

Thanks

Prickett

Check this out richardfoote.wordpress.com/2007/12/18/… – Thomasina

Yeah, actually I found out that article, but it's written in such an unnecessary commplicated way for basic question that I am asking that I couldn't go through it: "dropping/disabling a constraint policed by an automatically created Unique index causes the index to be dropped if you forget the KEEP INDEX clause". ... – Prickett

Answer 1

In the data model for your example email application it would not make sense to add a non unique index to the position attribute because each message has exactly one position and each position only contains one message; in this case the index should be unique.

But consider a possible "Sender" attribute. many messages can come from the same sender. If your application had a function to find all messages from a particular sender then it would make sense to add a non unique index on the sender column to improve performance on that operation.

Thirtieth

Answer 2

I think you are a bit confused about what a non-unique index means, in an attempt to clarify I will outline some points.

A database index is not an index in the same sense of an index of an array, and indexed values in databases are not necessarily associated with a particular number (or "index").

A database index is actually a data structure that stores the (usually sorted) data, and allows for fast access to specific values, that is the reason indexes are not created by default, as these data structures take space and should only be created if needed. If you'd like to explore such a data structure, you can take a look at B+ trees, which are one of the most common data structures used in indexing.

Now to address the point of a non-unique index, it should be pointed out that a non-unique index simply means an index that of a non-unique table column, so the index can contain multiple data entries with the same value, in which case the index is still very useful as it will allow fast traversal to the entries, even if some have duplicate values.

I hope I have helped clarify at least a little, and please correct me if I am mistaken in any part.

Nd