geode-docs/developing/query_index/indexing_guidelines.html.md.erb

---
title:  Tips and Guidelines on Using Indexes
---

<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements.  See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License.  You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->

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.

<a id="indexing_guidelines__section_A8885DFC334243508C4563C9692E0801"></a>
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](indexes_with_overflow_regions.html#concept_87BE7DB32C714EB0BF7532AF93569328) 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](create_multiple_indexes.html) for details.

## <a id="indexing_guidelines__section_A8AFAA243B5C43DD9BB9F9235A48AF53" class="no-quick-link"></a>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 <%=vars.product_name%> 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](query_index_hints.html)