An index in a database operates like the index tab on a file folder. It points out one identifying column, such as a customer's name, that makes it easier and quicker to find the information you want.
When you use index tabs in a file folder, you use those pieces of information to organize your files. If you index by customer name, you organize your files alphabetically; and if you index by customer number, you organize them numerically. Indexes in the database serve the same purpose.
You can use a single column to define a simple index, or a combination of columns to define a composite or compound index. To decide which columns to use, you first need to determine how the data in the table is accessed. If users frequently look up customers by last name, then the last name is a good choice for an index. It is typical to base indexes on primary keys (columns that contain unique information).
An index has the following advantages:
Faster row search and retrieval. It is more efficient to locate a row by searching a sorted index table than by searching an unsorted table.
In an application written with OpenEdge ABL (Advanced Business Language), records are ordered automatically to support your particular data access patterns. Regardless of how you change the table, when you browse or print it, the rows appear in indexed order instead of their stored physical order on disk.
When you define an index as unique, each row is unique. This ensures that duplicate rows do not occur. A unique index can contain nulls, however, a primary key, although unique, cannot contain nulls.
A combination of columns can be indexed together to allow you to sort a table in several different ways simultaneously (for example, sort the Projects table by a combined employee and date column).
Efficient access to data in multiple related tables.
When you design an index as unique, each key value must be unique. The database engine prevents you from entering records with duplicate key values.
Faster row search and retrieval. It is more efficient to locate a row by searching a sorted index table than by searching an unsorted table.