Datastore Indexes

App Engine predefines a simple index on each property of an entity. An App Engine application can define further custom indexes in an index configuration file named datastore-indexes.xml, which is generated in your application's /war/WEB-INF/appengine-generated directory . The development server automatically adds suggestions to this file as it encounters queries that cannot be executed with the existing indexes. You can tune indexes manually by editing the file before uploading the application.

Note: The index-based query mechanism supports a wide range of queries and is suitable for most applications. However, it does not support some kinds of query common in other database technologies: in particular, joins and aggregate queries aren't supported within the Cloud Datastore query engine. See Datastore Queries page for limitations on Cloud Datastore queries.

Index definition and structure

An index is defined on a list of properties of a given entity kind, with a corresponding order (ascending or descending) for each property. For use with ancestor queries, the index may also optionally include an entity's ancestors.

An index table contains a column for every property named in the index's definition. Each row of the table represents an entity in Datastore that is a potential result for queries based on the index. An entity is included in the index only if it has an indexed value set for every property used in the index; if the index definition refers to a property for which the entity has no value, that entity will not appear in the index and hence will never be returned as a result for any query based on the index.

Note: Datastore distinguishes between an entity that does not possess a property and one that possesses the property with a null value (null). If you explicitly assign a null value to an entity's property, that entity may be included in the results of a query referring to that property.

Note: Indexes composed of multiple properties require that each individual property must not be set to unindexed.

The rows of an index table are sorted first by ancestor and then by property values, in the order specified in the index definition. The perfect index for a query, which allows the query to be executed most efficiently, is defined on the following properties, in order:

  1. Properties used in equality filters
  2. Property used in an inequality filter (of which there can be no more than one)
  3. Properties used in sort orders

This ensures that all results for every possible execution of the query appear in consecutive rows of the table. Datastore executes a query using a perfect index by the following steps:

  1. Identifies the index corresponding to the query's kind, filter properties, filter operators, and sort orders.
  2. Scans from the beginning of the index to the first entity that meets all of the query's filter conditions.
  3. Continues scanning the index, returning each entity in turn, until it
    • encounters an entity that does not meet the filter conditions, or
    • reaches the end of the index, or
    • has collected the maximum number of results requested by the query.

For example, consider the following query:

Query q1 =
    new Query("Person")
                new FilterPredicate("lastName", FilterOperator.EQUAL, "Smith"),
                new FilterPredicate("height", FilterOperator.EQUAL, 72)))
        .addSort("height", Query.SortDirection.DESCENDING);

The perfect index for this query is a table of keys for entities of kind Person, with columns for the values of the lastName and height properties. The index is sorted first in ascending order by lastName and then in descending order by height.

Two queries of the same form but with different filter values use the same index. For example, the following query uses the same index as the one above:

Query q2 =
    new Query("Person")
                new FilterPredicate("lastName", FilterOperator.EQUAL, "Jones"),
                new FilterPredicate("height", FilterOperator.EQUAL, 63)))
        .addSort("height", Query.SortDirection.DESCENDING);

The following two queries also use the same index, despite their different forms:

Query q3 =
    new Query("Person")
                new FilterPredicate("lastName", FilterOperator.EQUAL, "Friedkin"),
                new FilterPredicate("firstName", FilterOperator.EQUAL, "Damian")))
        .addSort("height", Query.SortDirection.ASCENDING);


Query q4 =
    new Query("Person")
        .setFilter(new FilterPredicate("lastName", FilterOperator.EQUAL, "Blair"))
        .addSort("firstName", Query.SortDirection.ASCENDING)
        .addSort("height", Query.SortDirection.ASCENDING);

Index configuration

By default, Datastore automatically predefines an index for each property of each entity kind. These predefined indexes are sufficient to perform many simple queries, such as equality-only queries and simple inequality queries. For all other queries, the application must define the indexes it needs in an index configuration file named datastore-indexes.xml. If the application tries to perform a query that cannot be executed with the available indexes (either predefined or specified in the index configuration file), the query will fail with a DatastoreNeedIndexException.

Datastore builds automatic indexes for queries of the following forms:

  • Kindless queries using only ancestor and key filters
  • Queries using only ancestor and equality filters
  • Queries using only inequality filters (which are limited to a single property)
  • Queries using only ancestor filters, equality filters on properties, and inequality filters on keys
  • Queries with no filters and only one sort order on a property, either ascending or descending

Other forms of query require their indexes to be specified in the index configuration file, including:

  • Queries with ancestor and inequality filters
  • Queries with one or more inequality filters on a property and one or more equality filters on other properties
  • Queries with a sort order on keys in descending order
  • Queries with multiple sort orders

Indexes and properties

Here are a few special considerations to keep in mind about indexes and how they relate to the properties of entities in Datastore:

Properties with mixed value types

When two entities have properties of the same name but different value types, an index of the property sorts the entities first by value type and then by a secondary ordering appropriate to each type. For example, if two entities each have a property named age, one with an integer value and one with a string value, the entity with the integer value always precedes the one with the string value when sorted by the age property, regardless of the property values themselves.

This is especially worth noting in the case of integers and floating-point numbers, which are treated as separate types by Datastore. Because all integers are sorted before all floats, a property with the integer value 38 is sorted before one with the floating-point value 37.5.

Unindexed properties

If you know you will never have to filter or sort on a particular property, you can tell Datastore not to maintain index entries for that property by declaring the property unindexed. This lowers the cost of running your application by decreasing the number of Datastore writes it has to perform. An entity with an unindexed property behaves as if the property were not set: queries with a filter or sort order on the unindexed property will never match that entity.

Note: If a property appears in an index composed of multiple properties, then setting it to unindexed will prevent it from being indexed in the composed index.

For example, suppose that an entity has properties a and b and that you want to create an index able to satisfy queries like WHERE a ="bike" and b="red". Also suppose that you don't care about the queries WHERE