After you have designed the structure of the tables in the database, consider which columns are good candidates for the creation of indexes. An index is an object which you create after creating the table. It is not absolutely necessary to create an index on a table, but doing so can help ALLBASE/SQL point more quickly to the row you need. For example, in the Albums table, you might consider creating a unique index on the AlbumCode column, because using a unique index may allow SQLCore to arrive at the required row more quickly than by doing a scan of every row in the table. Also, the unique index guarantees that the AlbumCode is a unique number.
Suppose your application prints the album titles of all the albums containing selections by a specific composer. This requires a join between the Albums table and the Titles table. The join might be slow to execute because, first, ALLBASE/SQL would have to search every row of the Titles table to find the entries for the composer. Then it would have to search every row of the Albums table to find every match with the album code found in the Titles table along with the composer's name. The join column in this query is the AlbumCode column; if you create an index on the AlbumCode column of each table, the query might execute faster.
Furthermore, the AlbumCode column in the Albums table would be a good
candidate for creation of a unique index, because the value in this column should not be duplicated. In the case of the Titles table, the index should not be unique, because the table can have many rows with the same album code. That is, an album can contain many selections.
Some tables may also be good candidates for creation as hash structures, which are essentially self-indexed. For more information on this topic, refer to the chapter "Using SQL" in the ALLBASE/SQL Reference Manual.
