Creating Indexes

From NeoWiki

Indexes can help queries run more quickly. Base will automatically create indexes for primary and foreign keys when using an embedded database. But sometimes it is helpful to create indexes "manually." Indexes should be created for fields are joined in Queries (if Base has not already created them automatically) and for fields that you anticipate using "mathematical" criteria for in queries. (e.g. criteria like =, >, <>, or =>, but not the LIKE criteria).

For our example, we will create an index on the Date field in the Field_Trips table. This will make queries run faster when we filter the results by date.

1. Open the Field_Trips table for editing. (In the main database document window, click on Tables in the left hand column, then control-click or right-click on Field_Trips.)
2. Click on the Index Design button on the toolbar. It looks like a sheet of paper with a blue drafting triangle on top. The Indexes window opens.
3. Take note of the existing indexes.
   1. The index with a key icon next to it is the primary key index. It will have a name like SYS_IDX_49. (The number at the end may be different). If you click on it, the Fields section will reveal that the ID field is indexed. This area will be greyed out, as you cannot modify the primary key index.
   2. If you have followed the other steps of this tutorial, you will also see another index with a name like SYS_IDX_249. Clicking on this will reveal that the Indexed field is Location and that the sort order is ascending. This index is a foreign key index; it was created because the Field_Trips table relates to the Locations table through the Location field.

   Note the hsqldb documentation specifies that duplicate indexes will create memory and speed issues. Therefore always check to see what indexes already exist before building new indexes so you do not end up duplicating indexes.

4. Click the New Index icon in the Indexes window. It looks like a sheet of paper with a small sunburst on the right edge. A new index named index1 appears in the list
5. Click on the name index1; type a better name for the index (I chose Date_Index)
6. Click on the drop-down under Index field and choose the Date field.
7. Leave the Sort order set for Ascending. (The other option is Descending).
8. Click on the Save Current Index icon to save the index. (The save button looks like a sheet of paper with a "floppy" disk on it)
9. Click Close

Notes

• Indexes can be deleted by selecting the index from the list and clicking on the Delete Index icon. Do not delete indexes created automatically by Base (those which have names beginning with SYS_IDX).
• You can specify a unique index by checking the Unique box. See Creating a Unique Index for more information.