If you are not using Composite Indexes it may be possible that your queries are being performed as a sequential search.

For example; let's say you want to query a table named [Employee] with constraints for [Employee]Department and [Employee]Manager and [Employee]State - the 4D Code might looks like this:

Query([Employee];[Employee]Department="IS";*) Query([Employee];&;[Employee]Manager="Bob";*) Query([Employee];&;[Employee]State="California")

Without a composite index; the above query may do a sequential search like this:

First, Query the [Employee] table for records whose Department = "IS"
Second, Query the remaining records in [Employee] for records whose Manager = "Bob"
Third, Query the remaining records in [Employee] for records whose State = "California"

Individual indexes would speed up each query but it's still three separate queries.

When a composite index for "[Employee]Department and [Employee]Manager and [Employee]State" is used the database engine performs the comparison all at once per index key, instead of searching all of the data for one field, then all of the data for the next field, and so on and so fourth until all query constraints have been searched.

• NOTE: The actual path and plan of a query can be examined using the following commands:
  
  • DESCRIBE QUERY EXECUTION
  
  
  • Get Last Query Plan
  
  
  • Get Last Query Path
  
  For more information on these commands please see the following Tech Tip: How to get the last query path and plan?

Composite indexes can be created in either of the following ways:

1. Via the GUI: selecting the fields from the Structure editor, then right-clicking and choosing Create Composite Index:
   
   
   
   After choosing "New Composite Index" you will be presented with the following dialog that lets you name the index, choose the index type, and order the fields:
   
   
   
   
2. Via the CREATE INDEX command like this:
   
   

   ARRAY POINTER(fieldPtrArr;3) fieldPtrArr{1}:=->[Employee]Department fieldPtrArr{2}:=->[Employee]Manager fieldPtrArr{3}:=->[Employee]State CREATE INDEX([Employee];fieldPtrArr;Cluster BTree Index;"Emp_DeptManState")

NOTE: The Query order should match the order in which the fields were added to the Composite Index. For more information see: CREATE INDEX