CCNC/CCNC Module 5/The database application/Database Design/Database Indexes

From WikiEducator
Jump to: navigation, search
Tutorial.png Database Design 

Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs


Database Indexes

Icon objectives.jpg

Outcomes

Upon completion of this tutorial the learner will:
  • Be able to discuss the concept of database indexes
  • Describe how and when indexes should be applied


What are Database Indexes

Figure 1. Indexes are like file folder tabs

An index allows quick access to table data when the fields in the query have an index. The index reduces the amount of work the database has to do to find a record in the database. Without an index the database would have to look at EVERY record until it found the one it was looking for. When an index is applied to a field the database can use the index to significantly reduce the amount of work to find the specified record. It isn't that different than using file folders with index tabs so you can jump straight to the folder that contains the record you are looking for. Primary key values will have indexes automatically applied when the table is first created and most key value columns should be considered candidates for indexes (particularly a foreign key).

Applying Indexes

When you design a table you can define the indexes on one or more columns. These indexes will be utilized when you build database queries and reports. They will decrease the amount of time it takes for the specific data to be retrieved from the database. Designing indexes takes a little thought and can be done by asking yourself a few simple questions;

  1. what columns will I refer to when retrieving records? (example; a last_name column is often used in identifying a record, it would therefore be a good candidate for an index)
  2. is there a variety of values in the column I am considering for an index? (example; if your table had a column named country and 90% of the records were from the same country an index on the country column wouldn't help performance for most of the time it would still have to scan through almost all the records)

What do you mean by a scan? The term scan refers to a table scan and this occurs when a database search looks at every record in a table to find the record it is looking for. It is analogous to having to read every page of a book to find a subject of importance. In most situations you would look in the books index to find a reference to the subject and use the page number to find the information you seek.



Icon activity.jpg
Activity
Using the People and PhoneNumbers tables;
  • Can you identify three columns that would be good candidates for indexes?
  • Would it make sense for any indexes to be applied to two or more columns?




People
person_id first_name last_name birth_date gender
001 Bill Smith 28/12/1963 M
005 Fiona Jones 26/10/1961 F
012 Lisa Ballard 11/03/1965 F
PhoneNumbers
phone_type phone_number person_id
home (604) 555-1234 001
fax (604) 555-2345 001
cell (778) 555-2333 001
home (604) 555-4455 005
pager (604) 555-9988 005
home (604) 555-6543 012

Feedback.png




Icon inter.gif

Web Resources

Wikipedia has a few references relevant to indexes;



Test your knowledge



Icon qmark.gif

Database indexes

 

1. A gender field is a good candidate for an index?

TRUE
Incorrect, click here to review the topic. Gender fields usually are split 50/50, so an index wouldn't avoid a table scan on half the data.
FALSE
Absolutely Correct!

2. The person_id column from the above example will / should have an index because?

(a) It ends in "_id"
Incorrect, click here to review the topic.
(b) it is a primary key for the people table
Almost correct, e is the best choice
(c) it is a foreign key for the PhoneNumber table
Almost correct, e is the best choice
(d) it is the first column in the people table
Incorrect, click here to review the topic.
(e) both b and c
Absolutely correct!

Your score is 0 / 0