Tips and Guidelines on Using Indexes

Optimizing your queries with indexes requires a cycle of careful planning, testing, and tuning. Poorly-defined indexes can degrade the performance of your queries instead of improving it. This section gives guidelines for index usage in the query service.

When creating indexes, keep in mind the following:

• Indexes incur maintenance costs as they must be updated when the indexed data changes. An index that requires many updates and is not used very often may require more system resources than using no index at all.
• Indexes consume memory.
• Indexes have limited support on overflow regions. See Using Indexes with Overflow Regions for details.
• If you are creating multiple indexes on the same region, first define your indexes and then create the indexes all at once to avoid iterating over the region multiple times. See Creating Multiple Indexes at Once for details.

Tips for Writing Queries that Use Indexes

As with query processors that run against relational databases, the way a query is written can greatly affect execution performance. Among other things, whether indexes are used depends on how each query is stated. These are some of the things to consider when optimizing your Tanzu GemFire queries for performance:

• In general an index will improve query performance if the FROM clauses of the query and index match exactly.
• The query evaluation engine does not have a sophisticated cost-based optimizer. It has a simple optimizer which selects best index (one) or multiple indexes based on the index size and the operator that is being evaluated.
• For AND operators, you may get better results if the conditions that use indexes and conditions that are more selective come before other conditions in the query.
• Indexes are not used in expressions that contain NOT, so in a WHERE clause of a query, qty >= 10 could have an index on qty applied for efficiency. However, NOT(qty < 10) could not have the same index applied.
• Whenever possible, provide a hint to allow the query engine to prefer a specific index. See Using Query Index Hints