Menu
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
786
Unique indexes differ from nonunique indexes in enforcing uniqueness of the indexed attribute values for objects that are attached to the index. For example, you might want to populate Person objects into two indexes, a unique one on an “email” attribute, and a nonunique one on a “lastname” attribute.
In MySQL, UNIQUE INDEX is used to define multiple non-duplicate columns at once. While PRIMARY KEY constraint also assures non-duplicate values in a column, only one PRIMARY KEY can be defined per table. So for scenarios where multiple columns are to be made distinct, UNIQUE INDEX is used.
NON-UNIQUE An index that isn't unique simply has the option to have duplicate value, yet that fact isn't shown in description of database tables. For example, the author column could be indexed but have multiple occurrences of a given value within that column.
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values. Use the CREATE UNIQUE INDEX statement to create a unique index.
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.
105
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.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
