KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Few tips for indexing a field
PRODUCT: 4D Developer | VERSION: 11 | PLATFORM: Mac & Win
Published On: April 16, 2008
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

4D v11 SQL for first time offers not only B-Tree index, but also there are Cluster BTree, Composite and Keyword indexes available. They are very useful for searching and sorting any database, but if they are not chosen wisely, this can slow the above mentioned tasks and also can slow the performance of your desktop.

There are few common rules to follow.

1. If there is a field in the database that has to be unique, this field might be a good candidate for indexing.

2. Bear in mind the index selectivity (Number of different key divided by number of records). If it's closer to one (or 100 %) the BTree index will work perfectly for these fields.

3. If the field has very low index selectivity, Cluster BTree is a good choice.

4. Also you can apply composite index of several field, but try not to index too many fields, because this will slow the index maintenance.

5. If there is a indecision about which index to apply, select the automatic index. At this point the 4D optimiser will choose the optimal index for this field internally.

6. If you have a field with Keyword index on it, use the new comparison operator symbolized by the "%" character. This operator is available for the following commands: Query, Query By Formula and Query by Selection.

7. Never try to index BLOB or Picture fields. The system will not allow you!