It's a very good idea to put an index on key columns and on linking columns. A key column is a column that uniquely identifies rows. A linking, or common column, is a column that exists in two or more tables in order to establish a relationship between the tables. In effect, by choosing to link the common columns between two tables by adding a primary key and foreign key relationship, the columns are automatically indexed. You can also add a Unique key to a column, which is also automatically indexed. Primary keys, foreign keys, and unique keys are all types of constraints, which specifically control the data that enters your database by applying powerful data-integrity rules. By applying a constraint to a column, you can prevent irreconcilable and empty data from being entered add at the same time add an index.
Aside from constraints, an index can be added to a column for faster data retrieval in cases where the columns are not used in a primary key/foreign key relationship, and the column is likely to be used as part of a WHERE Clause whose values are at least moderately unique. In some cases, an index can be applied to columns that have RULES applied to them, which allows R:BASE to check the RULE faster. An index can also be added to the linking columns in views. An indexed column can contain null values, but R:BASE uses an index most efficiently if each row in the indexed column contains a value.
You can apply the following types of indexes:
•Unique index - Ensures that the values entered in the indexed column are unique
•NOT NULL Index - The index is populated with references to rows where the column value is not NULL. NULL values in the column are not added to the index. Not NULL indexes are ideal for columns which contain a considerable amount of NULL values, and where the not-NULL data is ideal for index retrievals. If a WHERE clause includes "ColName IS NULL" then the index is not used.
•Full or partial text index - For columns with NOTE or TEXT data types. R:BASE preserves each character in the indexed column (a full text index). Or, you can specify the number of characters to preserve, and R:BASE hashes (converts characters to a 4-byte integer) the remaining characters (a partial index).
To keep the index file from becoming too large, use a partial index--specify enough characters to guarantee the values are unique. If the preserved values are not unique, R:BASE must unhash the values before it can identify the rows, which slows performance. If you do not specify the number of characters to preserve, R:BASE preserves all of them, unless there are more than 200 characters defined; then, R:BASE preserves the first 32 and hashes the rest.
•Multi-column index - A combination of up to 8 columns in one index. For example, if you consistently search three columns when working with a certain database, you can define a separate index for each column. Or, you can define one index for all three columns. R:BASE searches a multi-column index faster than three separate indexes. Multi-column indexes should be constructed in the same order as used in the WHERE Clause (that is to mix ASC and DESC in the index based on actual use). With a query upon rows by date and listing the results in descending order, so the most current invoices appear first, it is important to define the index on the table's date column in descending order as well. In a situation where you need to use more than one column to uniquely identify a row, try combining them into a computed column and then indexing the computed column.
Primary, unique, and foreign key columns are indexed automatically. In addition, the following types of columns are good for indexing:
•Columns that are neither primary nor foreign keys, but are frequently referred to in queries and sorts.
•Columns that have rules applied to them--in most cases, R:BASE can use indexing to check rules faster.
•Linking columns in views.
Although indexes speed up searches, they may slow down data entry for 2 reasons:
•they occupy space on the disk in the number 3 database file
•it takes time to build the index for each value as it is entered
Therefore, try to limit the use of indexes.
