### Indexed tables

Creating indexes for database tables can increase the speed of data access and reduce the time it takes for the program to evaluate data. Some DBMS applications automatically index your database tables, while others require that you create an index yourself. For the best report generation performance, make sure each of your database tables has a corresponding index.

Note: Some DBMS applications do not support indexed tables. Refer to the documentation for your DBMS to find out if it supports indexes and how to create them. If your DBMS documentation does not mention indexed tables, it may not support them, and you should link tables based on common fields. The Visual Linking Expert can also help you determine if your tables include indexes.

Indexes organize the records in a relational database table so that data can be located easier. For example, assume you have a table with the following data:

Order# | Customer | Amount
--- | --- | ---
10444 | Allez Distribution | 25141.50
10470 | BG Mountain Inc. | 19164.30
10485 | Sierra Mountain | 8233.50
10488 | Mountain Toad | 24580.50
10495 | SFB Inc. | 7911.80
10501 | La Bomba de Bicicleta | 1956.20
10511 | BG Mountain Inc. | 1683.60
10544 | Sierra Bicycle Group | 19766.20
10568 | Mountain Tops Inc. | 29759.55
10579 | Sierra Bicycle Group | 12763.95

The information in this table is organized according to the Order# field. This is fine anytime you want to look up information in the table based on order numbers. However, what if you want to look up information specific to a certain customer?

Say you want to look up all orders made by Sierra Bicycle Group. The database engine must begin by looking at the first order number in the list and checking to see if the customer name matches the request. If not, it goes to the second order number, and checks that customer name. When an order number is reached that contains the correct customer name, the database engine retrieves the information, then continues to the next order number. Using this technique, both the Order# field and the Customer field must be read for every single record in the table. This takes a long time and a large amount of computer processing effort for examining extensive database tables with thousands, or even millions of records.

Instead, you can create an index for the table based on the Customer field. Such an index might look like this:

Customer | Pointer to Order#
--- | ---
Allez Distribution | 10444
BG Mountain Inc. | 10470
BG Mountain Inc. | 10511
La Bomba de Bicicleta | 10501
Mountain Toad | 10488
Mountain Tops Inc. | 10568
SFB Inc. | 10495
Sierra Bicycle Group | 10544
Sierra Bicycle Group | 10579
Sierra Mountain | 10485

In this index, information is organized by customers, not order numbers. Also, notice that the second column actually contains pointers to specific order numbers in the original table. By using this index, the database engine can search just the information in the Customer column until it finds the customer you are interested in, Sierra Bicycle Group.

For each correct customer entry the database engine finds in the index, it looks up the matching order in the table according to the pointer in the second column of the index. Only the orders for the correct customer are read. Finally, since information in the index is organized according to the customer names, the database engine does not need to continue searching through the index or the table as soon as it finds an index entry that does not match the requested customer.

The advantage of this highly organized search through a database table according to an index is speed. Using indexes speeds up data retrieval and report generation, important factors when reporting on large database files.

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com