While indexes might very well be the single most important key in database performance tuning, they come at the price of redundancy.
There are two main disadvantages tightly connected to redundant data storage:
- The danger of inconsistencies between the redundant copies of data that should be at all times identical.
- Increased storage and memory consumption because the same data is physically duplicated.
Fortunately, the former is a non-issue with indexes. As the database server takes care of keeping data and indexes consistent without human intervention, you cannot get into a situation where two columns that should contain equal data at all times are out of sync due to programming errors or the like. This is usually a problem when violating normalization rules.
In contrast to that, there is no way to prevent the latter disadvantage. We need to store multiple copies if we want different sort orders for quick lookups. What we can do, however, is to attempt...