When applied properly, indexes decrease the time it takes to get data from the database. However, when indexes are used poorly, or not at all, it will take longer for R:BASE to return data. When properly implemented, indexes can greatly improve the data retrieval performance of your applications.
When adding an index to a column, consider the following criteria:
•Columns that are not primary, foreign, or unique keys, but are frequently referred to in queries and sorts
•Columns that have rules applied to them
•Linking columns in views
•The index is more efficient if each row contains a value
•The index is more efficient based on the uniqueness of the data. Check the Duplicate Factor and Adjacency Factor values within the Data Designer.
In order to maintain index efficiency, review the following drawbacks to avoid:
1.Indexes added to a column where data duplication is high
The single most important factor in determining the effectiveness of an index is the uniqueness of index values. A unique index value is found faster than a value with multiple index occurrences.
2.Indexes added to an existing Primary Key, Foreign Key, Unique Key
Primary Key, Foreign Key, and Unique Key are all type of constraints that are automatically indexed when added to a table. Avoid indexing these keys as this creates a double index, and makes R:BASE perform twice as much work to retrieve the same data.
3.Indexing column where NULL values exist
An index is more efficient if each row contains a value. A column with many NULL records will slow down the index.
4.Lengthy text column indexes
The fastest, most efficient data types for indexed access are INTEGER, REAL, DATE, TIME, and TEXT with a defined length of four characters or less. It advised that you avoid lengthy TEXT column indexes.
5.Common column searches that are not multi-column indexes
If you consistently search three columns when working with a database, you can define a separate index for each column, but it is better to define a multi-column index for all three columns. This is because R:BASE searches a multi-column index faster than three separate indexes.
6.Ascending/Descending "ORDER"
Data retrieval can be increased in an ORDER BY clause when the column or columns listed in the ORDER BY clause, are included in an index, with the same column sort order, as specified in the ORDER BY clause.
For example, processing data by invoice date in a descending order would be faster as those dates are likely to appear first. So, when defining the index for invoice date, the index should be created in descending order as well.
7.Not using indexes where needed
Over time, the structure of a database is likely to change. Periodically, you should check for places where indexes can be added to improve speed.
For a more in-depth review of indexes, review the "Indexing Explained" technical document available at the From The Edge Web site.
