CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout/Adding Indexes

---

Adding indexes to a Table

Outcomes

Upon completion of this tutorial the learner will be able to: Add an new index to a table Rename an index

Adding indexes to a table

Figure 1. Index design

As mentioned in the previous section it is important to add indexes to tables as they increase data retrieval performance. After adding new fields to the PhoneNumbers table it is beneficial to add indexes to certain fields.

Activity

Reviewing the modified PhoneNumbers table; Can you identify which fields could benefit from indexes? What would be the rationale for applying each index?

Adding the index

Figure 2. Adding Indexes

Once the Index design dialog is available the new index button should be pressed to create a new index. When you open the index designer you may notice indexes are already present. These are indexes created when you set the primary and foreign keys.

Naming the index

Figure 3. Renaming the index

Figure 4. Renamed

Once you have created the new index the field needs to be chosen and the sort order set. A meaningful name should be given to the index where the name of the field being indexed is a part of the indexes name. This will be helpful in identifying the index. You can create many indexes and they will all be saved once you press the 'Save' button on the index design dialog.

Test your knowledge

Indexes

1. Indexes are also created when you add primary and foreign keys? TRUE → Absolutely Correct! FALSE → Incorrect, primary and foreign keys are essentially indexes. Click here to review the topic.. 2. Which of the following would be good as parts of names for indexes? (a) _idx_ → Almost correct, all these suffixes could be good in naming indexes. (b) _pk_ → Almost correct, all these suffixes could be good in naming indexes. (c) _fk_ → Almost correct, all these suffixes could be good in naming indexes. (d) all of the above would be good suffixes → Absolutely Correct! (e) None of the above → Incorrect, click here to review the topic. Your score is 0 / 0