4D v14.3Creating and modifying indexes |
||
|
4D v14.3
Creating and modifying indexes
Creating and modifying indexes
You can associate indexes fields that you frequently use for searching and sorting. For example, you might index Last Name, Company name, or Product name if you plan to search for specific records or sort the records by these fields. You also use this property for fields that establish relations between tables. For more information about this, refer to Creating and modifying relations. When an index is associated with a field, 4D creates an internal index table for the field. This table allows 4D to perform rapid searches and sorts on the field. When searching or sorting on an unindexed field, 4D moves through data sequentially, examining each record in order. Indexing allows 4D to search and sort without going through every record. You can index fields of the Alpha, Text, Date, Time, Boolean, Integer, Long integer, Integer 64 bits, Real, Float and Picture type. As you add and delete records, 4D automatically updates its index table. If you create an index for a field that already exists, 4D automatically indexes the existing data. You can specify as many indexed fields as you want. Indexes are also rebuilt during specific operations such as conversion of earlier databases or data compacting. Do not index every field. An index increases the size of the database, using more space on disk. Using many indexes also increases the time needed to save a record since 4D updates the index table with each record validation. Indexed fields are displayed in bold type in the Structure editor. 4D provides different types of indexes. Choosing between the different types is generally based on the result expected and the type of data present in the field. There are three main types of indexes:
A standard index is intended to accelerate database operations (a standard index refers to a generic index as opposed to a keyword or composite index). 4D offers two types of architectures for standard indexes: B-Tree and Cluster B-Tree.
When you choose the index architecture, 4D also provides the Automatic option. In this case, 4D automatically selects the architecture according to the type of data concerned. Note: A B-Tree index associated with a Text type field stores the first 1024 characters of the field (maximum). Therefore in this context, searches for strings containing more than 1024 characters will fail. Composite indexes store the combined values of two or more fields for each entry. The classic example is a composite index based on the FirstName+LastName fields. Searching for “Peter Smith” will therefore be optimized compared with a standard search (searching for “Smith” then searching for “Peter”). 4D automatically takes advantage of composite indexes during queries and sorts. For example, if a composite index “City+ZipCode” exists, it will be used in the case of a query of the type “lastname=carter and city=new york and zipcode =102@”. In the structure editor, composite indexes can only be created using the index creation dialog box. For a detailed description of this dialog box, refer to the “Creating an index” section below. You can use a specific type of index with Alpha, Text and Picture fields: a keyword index.
You can use the DISTINCT VALUES command to get the list of keywords contained in an keywords index. You use picture or text keyword indexes through the % operator: this operator must be placed in the query or sort formulas in order to specifically use an index value. For example: QUERY([PICTURES];[PICTURES]Photos %"cats") This works the same way for all the query and order by commands: QUERY BY FORMULA, QUERY SELECTION, ORDER BY, etc. For more information about how the % operator works and about keyword searches, refer to Comparison Operators in the 4D Language Reference manual. The button of the toolbar in the Structure editor displays the Index List window. This window displays the list of all the indexes of the structure, regardless of their type: button displays the index property dialog box.
The way an index is created will depend on its type. In addition, you can choose to create an index directly or to use the index creation dialog box. To create a standard index directly:
To create a keyword index directly:
To create a composite index or any other type of index using the index creation dialog box:
To add a field to the index, click on the button. The list of fields of the selected table is displayed so that you can indicate the field to be added to the index.
To delete a field from the index, select it in the list and click on the button. You can delete indexes that are no longer useful at any time. This can be carried out directly in the Structure editor or using the List Index window. For more information about the List Index window, please refer to the “Index List” section above. To delete a standard index:
To delete a keyword index:
The deletion (and viewing) of a composite index can only be carried out from the List Index window (using the button). You can reindex a field at any time; in other words, rebuild the index table(s) associated with it, in accordance with the data present. This can be useful in the case of application maintenance. Reindexing can be carried out using the Rebuild command in the Index List window. Note that modifying the data language (see Text comparison) or maintenance operations such as compacting (see Compact page) will also cause the indexes to be rebuilt. |
PROPERTIES
Product: 4D ARTICLE USAGE
4D Design Reference ( 4D v14 R2) Inherited from : Creating and modifying indexes ( 4D v12.4) |