Using Indexes on Single Region Queries

Queries with one comparison operation may be improved with either a primary key or functional key index, depending on whether the attribute being compared is also the primary key.
SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123'
If pkid is the key in the /exampleRegion region, creating a primary key index on pkid is the best choice as a primary key index does not have maintenance overhead. If pkid is not the key, a functional index on pkid should improve performance.
With multiple comparison operations, you can create a functional index on one or more of the attributes. Try the following:
  1. Create a single index on the condition you expect to have the smallest result set size. Check performance with this index.
  2. Keeping the first index, add an index on a second condition. Adding the second index may degrade performance. If it does, remove it and keep only the first index. The order of the two comparisons in the query can also impact performance. Generally speaking, in OQL queries, as in SQL queries, you should order your comparisons so the earlier ones give you the fewest results on which to run subsequent comparisons.
For this query, you would try a functional index on name, age, or on both:
SELECT DISTINCT * FROM /exampleRegion portfolio 
WHERE portfolio.status = 'active' AND portfolio.ID > 45

For queries with nested levels, you may get better performance by drilling into the lower levels in the index as well as in the query.

This query drills down one level:
SELECT DISTINCT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE positions.secId = 'AOL' and positions.MktValue > 1