CCNC/CCNC Module 5/The database application/Database Design/Database Indexes
 |
Database Design |
Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs |
Contents
Database Indexes
Upon completion of this tutorial the learner will:
|
What are Database Indexes
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;
- 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)
- 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.
Using the People and PhoneNumbers tables;
|
| 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 |
| 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 |
- The person_id field in the People table is a primary key and by default is indexed.
- The person_id field in the PhoneNumbers table is a foreign key and by default is indexed.
- The last_name field is a good candidate for an index for it is common to search a database table on last_name
- The birth_date field would also be a good candidate for often searches are date based
- The phone_type field is also a good candidate for an index.
| Wikipedia has a few references relevant to indexes; |
Test your knowledge
|
|
