Designing a Database and Relational Schema

part3.pdf

Home >Information Systems homework help > Designing a Database and Relational Schema

Media Audio Document

Topic: Internals of Indexes

Since a library contains so many books, we need to have some help in finding the particular book we are interested in. Most libraries are far too large for us to walk around hoping we will see our book as we walk by! Fortunately, the library helps us by providing the library catalog to assist in locating a particular book. The library catalog is indexed by title, topic, and author to make it possible for us to find the location of a book by a particular author or title, or simply browse books related to a topic of interest.

The index in a database system is similar to the index in a library catalog. Conceptually, a database index contains a key value and a collection of pointers that store the location of the data identified by the key.

Why would you want to create an index in a database? One of the main purposes is to improve the performance of a database query. An index can quickly return the rows that match a query; without an index, every row in a table must be examined to see if it matches the query condition. For large tables, an index can represent a significant increase in performance.

Dave from Dave's Database Designers has designed a database for Priscilla, the owner of Priscilla's Pampered Pets pet store. One of the tables from that database is shown below.

Pet Table Name Age Species PetId OwnerId Phydeaux 3 Dog 1 4 Felix 5 Cat 2 1 Polly 10 Parrot 3 2 Iggie 2 Iguana 4 3 Bugs 3 Rabbit 5 2 Toto 23 Dog 6 5 Cheshire 12 Cat 7 5

Creating an index on a column used as a search key, in comparison operations in a conditional expression, or to order the rows in a table is a common practice in relational databases. An index would be columns often used in the WHERE, ORDER BY, and GROUP BY clauses of a SELECT statement.

Dave decides that since Priscilla often needs to locate all the pets who belong to a particular pet owner, he creates an index on the Owner column for the Pet table by using the following command:

CREATE INDEX OWNERINDEX ON PET(OWNER);

Now, when the following structured query language (SQL) command is used to perform a query, the OwnerIndex will be used to locate Dilbert's pets. The operation will be quicker than before the index was created.

SELECT * FROM PET WHERE OWNER = 'DILBERT';

Sometimes, it is helpful to create an index using multiple columns in a table. This is known as a composite index. From his conversations with Priscilla, Dave concludes it will be a good idea to create an index for the Pet table based on the Owner, Species, and Name columns. So he executes this command:

CREATE INDEX PETINDEX ON PET(OWNER, SPECIES, NAME);

Composite database indexes should be used rarely, because they probably require a lot of memory and processing power to maintain. The one situation in which a composite index can be helpful is when the combination of the columns always contains a unique set of values. For example, if each pet owner names each pet of a particular species with a different name (that is, every dog has a different name), then the PetIindex can be specified to be unique. Dave decides to make the composite index unique. He uses the following command to do so:

CREATE UNIQUE INDEX PETINDEX ON PET(OWNER, SPECIES, NAME);

The database will now guarantee that every row in it will have a unique combination of the Owner, Species, and Name column values. This guarantee of uniqueness is another use of database indexes.

You have now seen how an index can improve the performance of a database query. You have also seen how to create a database index. Most indexes are based on only one column in a table. However, you can also create a composite index—an index that uses multiple columns from a table.