inm logo
Corporate Website | Contact | Store | Support | Login  
     


Knowledgebase
View categorized listing of all common frequently asked questions.
Downloads
View our categorized library of downloads for all necessary manuals, software, etc.
 Article Options
Support Center » Knowledgebase » INM V12 Database for Director » Optimizing your database using indexes
Support Page Banner
 Optimizing your database using indexes
Product:Platform:Area:Version:
INM V12 Database for Director Windows, Macintosh Structure and indexes All

Summary

How to optimize your database.

Example

Two methods in INM V12 Database take advantage of database indexes: mOrderBy and mSelect.

mSelect chooses the index that delivers the fastest search time based on the database structure. It does an excellent job most of the time. However, it can be fooled in some extreme cases where the actual data in the database is not uniform. In such cases, you can optimize your queries to further improve searching time.

mOrderBy indifferently uses any one index defined for the field it sorts (if more than one such index is defined). There is no performance handicap in using one index or another. However, at any time, only one index can be used by INM V12 Database. Thus, if you call mOrderBy and mSelect in a single query, INM V12 Database uses the best index it can to build the selection, and then sorts the selection without relying on indexes.

mSelect chooses it best index based on the following algorithm*. Assume that your query is:

(Field1 = A) AND (Field2 starts B) AND (Field3 < C)

INM V12 Database first checks if Field1 is the first segment of an index (i.e., a simple index is defined for it, or it is the first field of a compound index). If such an index exists, it is automatically considered to be the best index for the query. Otherwise, INM V12 Database checks if Field2 is the first segment of an index. If so, that index is the best index for the query. Otherwise, INM V12 Database attempts to apply the same logic to Field3. If none of Field1, Field2 and Field3 is indexed, or appears in the first segment of a compound index, the table's default index is used.

(*Note that this logic does not apply to OR operators. AND operators further refine a selection, whereas OR operators constantly add new data to them. This is also why queries with ORs are slower than those with ANDs.)

The index chosen by mSelect determines the selection's default sorting. Thus, if Field1 is not indexed and Field2's index is chosen, the following script yields a selection sorted by Field2:

mSetCriteria(gT, "Field1", "=", A)
mSetCriteria(gT, "AND", "Field2", "starts", B)
mSetCriteria(gT, "AND", "Field3", "<", C)
mSelect(gT)

Further assume that Field2 contains a lot of duplicate values, the sub-sorting order of the duplicate records would be determined by the chosen index's second segment, or the order in which records are input if the chosen index is not compound.

As a result, you can control the sorting order of your selection without calling mOrderBy. By instance, consider the following address book table:

[TABLE]
Addresses
[FIELDS]
LastName string
FirstName string
YearOfBirth integer
[INDEXES]
LastNameNdx duplicate LastName ascending
FirstNameNdx duplicate FirstName ascending

The following query uses the index LastNameNdx. It delivers a selection sorted by LastName and then by order of input. Since all the last names in the selection would be identical ("Smith"), the selection's sorting order would be the order in which records were added to the database.

mSetCriteria(gT, "LastName", "=", "Smith")
mSelect(gT)

If you need to sort all the Smiths in your table by order of First Name, run the following script:

mSetCriteria(gT, "LastName", "=", "Smith")
mOrderBy(gT, "FirstName")
mSelect(gT)

You can optimize this script by slightly modifying your database structure as follows:

[TABLE]
Addresses
[FIELDS]
LastName string
FirstName string
YearOfBirth integer
[INDEXES]
LastNameNdx duplicate LastName ascending
FirstName ascending FirstNameNdx duplicate FirstName ascending

By adding FirstName to the LastNameNdx index, your initial script:

mSetCriteria(gT, "LastName", "=", "Smith")
mSelect(gT)

The precedent statement gives a selection sorted by FirstName.

You can further optimize your queries by imposing a specific index for your search, if more than one index fits the best index criteria.

Assume, for example, that your table contains two compound indexes: one for LastName/FirstName and one for LastName/YearOfBirth:

[TABLE]
Addresses
[FIELDS]
LastName string
FirstName string
YearOfBirth integer
[INDEXES]
LastFirstNdx duplicate LastName ascending FirstName ascending LastBirthNdx duplicate LastName ascending YearOfBirth ascending FirstNameNdx duplicate FirstName ascending

The following query automatically chooses LastFirstNdx as its best index, thus delivering a selection sorted by LastName and then FirstName.

mSetCriteria(gT, "LastName", "=", "Smith")
mSelect(gT)

If you need your selection sorted by LastName and then YearOfBirth, run the following script:

mSetCriteria(gT, "LastName", "=", "Smith")
mSetCriteria(gT, "AND", "YearOfBirth", ">", 0) -- bogus criterion
mSelect(gT)

The criterion (YearOfBirth > 0) does not affect your selection in any way (since any data stored in this field is always greater than 0). However, its presence forces INM V12 Database to use the LastBirthNdx index thus resulting in a selection sorted by YearOfBirth.



Article Details
Article ID: 111
Created On: 26 Sep 2006 02:38 PM

 This answer was helpful  This answer was not helpful

inm general footer
Services Xtras Go Products Support Gallery Store Download About Us Contact Newsroom