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

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.


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.