Database Index

What is an Index?

• A database index is a column of a table that allows records of the respective table to be retrieved more quickly.
• A database index is most often used a primary key for a record.
• This requires extra space on disk, but will help speed up your database.
• Indexing allows efficient searching to be performed on a data set

Design without Index

• Assume we have created a Table to account for our employees.
• Assume our first hire is a 24 year old named Leon Hunter.
• Assume our second hire is a 24 year old named Leon Hunter.
• Assume our objective is to find the age of a particular Leon Hunter.
• The above constraints support storage of indistinct records.
• The constraints support no way to query for a “particular `Leon Hunter”, as the data returned will not be distinct.

First Name	Last Name	Age	Gender
Leon	Hunter	24	Male
Wilhem	Alcivar	23	NULL
Nhu	Nguyen	NULL	Female
Leon	Hunter	24	Male

Design with Index

• Add a column named ID and denote column as PRIMARY KEY to make the column an index.
• Searching for a row by its ID help us retrieve only the desired data and also improve speed by a measurable degree.

ID	First Name	Last Name	Age	Gender
1	Leon	Hunter	24	Male
2	Wilhem	Alcivar	23	NULL
3	Nhu	Nguyen	NULL	Female
4	Leon	Hunter	24	Male

---

Relational Data Using Index

Problem

• Let’s say we keep a list of phone numbers now.
• Same issue as before.
• That owner there refers to only one person now, but how can we make sure that we match it to the correct person?

ID	Phone Number	Phone Owner
1	555-321-4547	Wilhem
2	555-221-4548	Leon
3	555-782-4549	Nhu

Solution

• Use the person’s unique id to identify who this number belongs to.
• To find out who owns the phone number, we take that id and search for it in the Person list that we made above.
• This is what we call a relationship.
• Relational data uses these kinds of relationships.

ID	Phone Number	Phone Owner ID
1	555-321-4547	2
2	555-221-4548	1
3	555-782-4549	3