Keeping data from turning into junk keeps your database relevant and useful for a long time. Having speedy results will also keep your users happy and interested in using your database for a long time.
What Is an Index and How Is It Used?
Indexes help speed up the data retrieval when there are large numbers of records being searched.
Typically, you should consider your primary keys, foreign keys, secondary keys, and any other heavily searched fields as good index candidates. (Most DBMS automatically generate an index from the primary keys.)
When and How Should an Index Be Used?
Indexes should be used when there is a large number of records being queried and speed is an issue, such as within a web page. Primary and foreign keys are the most logical, and first fields which need indexing to speed up data retrieval.
Why wouldn’t I just index every field in the table? Wouldn’t that speed up the process overall?
That sounds like a great idea, but it's not any more advantageous than if you hadn't added an index in the first place. The perk of early boarding for first-class or frequent flyer passengers would be lost if you passed it on to everyone. The same is true when you look for fields to index.
How Do I Make Effective Indexing Selections?
Selecting good indexes will allow you to speed up database transactions like queries and new record inserts. Creating effective indexes begins with choosing the most unique values in a table. Start with the primary and foreign keys and test your database transactions to see if you are seeing a noticeable increase in performance.
The next step would be a clustered index where you select two or more attributes from a table, which would give you either a unique or near-unique combination to help speed up database transactions. Indexing the primary and foreign keys, and a combination of other attributes, typically gives you what you need to increase DBMS performance.
Let’s Recap!
Indexes help speed up database transactions like selecting or updating records.
Unique or mostly-unique attributes are the most effective means of setting indexes that produce the best results.
Let's check your understanding of the design skills we've been putting into practice.
