Queries with one comparison operation may be improved with either a key or range index, depending on whether the attribute being compared is also the primary key.
If pkid is the key in the /exampleRegion region, creating a key index on pkid is the best choice as a key index does not have maintenance overhead. If pkid is not the key, a range index on pkid should improve performance.
SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123'
With multiple comparison operations, you can create a range index on one or more of the attributes. Try the following:
For this query, you would try a range 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