What is the purpose of custom indexes in a database?
I have searched, but cannot find a suitable explanation of 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 described at http://railsguides.net/advanced-rails-model-generators/
I don't understand: if the purpose of an index is to "set a shortcut" to the position of a value in a table for quicker access to it, then how can multiple values have the same index?
Let's say for example I store emails in a table and I want to index their value positions. If so far I'm right, if I have unique indexes, then the DB can be indexed by foo@bar.com at position 150, and bar@foo.com is indexed at position 150 as well. So if I end up I'll end up with 100 different values at position 150, wouldn't that defeat the indexing goal in the first place if the DB still had to search for all values in 150 to find the record I needed?
How does it make sense?
thank
source to share
In the data model for your sample email application, it would be pointless to add a non-standard index to the position attribute, because each message has exactly one position, and each position contains only one message; in this case, the index must be unique.
But consider a possible "Sender" attribute. many messages may come from the same sender. If your application had a feature to find all messages from a specific sender, then it might be wise to add a custom index on the sender column to improve the performance of this operation.
source to share
I think you are a little confused about what non-unique index means, in an attempt to clarify, I will outline some points.
A database index is not an index on a single array index value, and indexed values in databases are not necessarily associated with a specific number (or "index").
A database index is actually a data structure that stores (usually sorted) data and allows you to quickly access certain values, that is, the reasons why indexes are not created by default, since these data structures take up space and should only be there if needed. If you want 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 notion of a non-historical index, it should be pointed out that a non-ideal index simply means an index that has a non-unique table column, so the index can contain multiple data records with the same value, in which case the index is still very useful because it will allow you to quickly bypass records, even if some have duplicate values.
Hope I helped clarify at least a little, and please correct me if I am wrong about any part.
source to share