Note:Developers building new applications arestrongly encouraged to use theNDB Client Library, which has several benefitscompared to this client library, such as automatic entity caching via the MemcacheAPI. If you are currently using the older DB Client Library, read theDB to NDB Migration Guide

App Engine predefines a simple index on each property of an entity.An App Engine application can define further custom indexes in anindexconfiguration file namedindex.yaml. The development server automaticallyadds 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 formost applications. However, it does not support some kinds of query common inother database technologies: in particular, joins and aggregate queries aren'tsupported within the Datastore query engine. See Datastore Queries page for limitations on Datastore queries.

Index definition and structure

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

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

Note: Datastore distinguishesbetween an entity that does not possess a property and one that possesses theproperty with a null value (None). If you explicitly assign anull value to an entity's property, that entity may be included in the resultsof a query referring to that property.

Note:Indexes composed of multiple properties require that each individual propertymust not be set tounindexed.

The rows of an index table are sorted first by ancestor and then by propertyvalues, in the order specified in the index definition. Theperfect index fora query, which allows the query to be executed most efficiently, is defined onthe following properties, in order:

1. Properties used in equality filters
2. Property used in an inequality filter (of which there can beno more thanone)
3. Properties used in sort orders

This ensures that all results for every possible execution of the query appearin consecutive rows of the table. Datastore executes a queryusing 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 ofthe 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 (stated in GQL):

SELECT*FROMPersonWHERElast_name="Smith"ANDheight <72ORDERBYheightDESC

The perfect index for this query is a table of keys for entities of kindPerson, with columns for the values of thelast_nameandheight properties. The indexis sorted first in ascending order bylast_nameand then in descending order byheight.

To generate these indexes, configure your indexes like this:

indexes:-kind:Personproperties:-name:last_namedirection:asc-name:heightdirection:desc

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

SELECT*FROMPersonWHERElast_name="Jones"ANDheight <63ORDERBYheightDESC

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

SELECT*FROMPersonWHERElast_name="Friedkin"ANDfirst_name="Damian"ORDERBYheightASC

and

SELECT*FROMPersonWHERElast_name="Blair"ORDERBYfirst_name,heightASC

Index configuration

By default, Datastore automatically predefines an index for eachproperty of each entity kind. These predefined indexes are sufficient to performmany simple queries, such as equality-only queries and simple inequalityqueries. For all other queries, the application must define the indexes it needsin anindex configurationfilenamedindex.yaml. If the application tries to perform a query that cannot be executed with the available indexes (either predefined orspecified in the index configuration file), the query will failwith aNeedIndexError exception.

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 arelimited to a singleproperty)
• Queries using only ancestor filters, equality filters on properties, andinequality filters on keys
• Queries with no filters and only one sort order on a property, eitherascending or descending

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

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

When two entities have properties of the same name but different value types, anindex of the property sorts the entities first byvalue type and then by asecondary orderingappropriate to each type. For example, if two entities each have a propertynamedage, one with an integer value and one with a string value, the entitywith the integer value always precedes the one with the string value when sortedby theage property, regardless of the property values themselves.

This is especially worth noting in the case of integers and floating-pointnumbers, which are treated as separate types by Datastore.Because all integers are sorted before all floats, a property with the integervalue38 is sorted before one with the floating-point value37.5.

Unindexed properties

If you know you will never have to filter or sort on a particular property, youcan tell Datastore not to maintain index entries for thatproperty by declaring the propertyunindexed. This lowers the cost of runningyour application by decreasing the number of Datastore writes ithas to perform. An entity with an unindexed property behaves as if the propertywere not set: queries with a filter or sort order on the unindexed property willnever match that entity.

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

For example, suppose that an entity has propertiesa andb andthat you want to create an index able to satisfy queries likeWHERE a ="bike" and b="red". Also suppose that you don't careabout the queriesWHERE a="bike" andWHERE b="red".If you seta to unindexed and create an index fora andbDatastore will not create index entries for thea andb index and so theWHERE a="bike" and b="red" query won'twork. For Datastore to create entries for thea andb indexes, botha andb must be indexed.

You declare a property unindexed by settingindexed=False in theproperty constructor:

classPerson(db.Model):name=db.StringProperty()age=db.IntegerProperty(indexed=False)

You can later change the property back to indexed by calling the constructoragain withindexed=True:

classPerson(db.Model):name=db.StringProperty()age=db.IntegerProperty(indexed=True)

Note, however, that changing a property from unindexed to indexed does notaffect any existing entities that may have been created before the change.Queries filtering on the property will not return such existing entities,because the entities weren't written to the query's index when they werecreated. To make the entities accessible by future queries, you must rewritethem to Datastore so that they will be entered in the appropriateindexes. That is, you must do the following for each such existing entity:

1. Retrieve (get) the entity from Datastore.
2. Write (put) the entity back to Datastore.

Similarly, changing a property from indexed to unindexed only affects entities subsequently written to Datastore. The index entries for anyexisting entities with that property will continue to exist until the entitiesare updated or deleted. To avoid unwanted results, you must purge your code ofall queries that filter or sort by the (now unindexed) property.

Index limits

Datastore imposeslimits on the numberand overall size of index entries that can be associated with a single entity.These limits are large, and most applications are not affected. However, thereare circumstances in which you might encounter the limits.

As describedabove,Datastore creates an entry in a predefined index for everyproperty of every entity except long text strings(Text) and long byte strings(Blob) and those you have explicitlydeclared asunindexed. The propertymay also be included in additional, custom indexes declared in yourindex.yaml configurationfile. Provided thatan entity has no list properties, it will have at most one entry in each suchcustom index (for non-ancestor indexes) or one for each of the entity'sancestors (for ancestor indexes). Each of these index entries must be updatedevery time the value of the property changes.

For a property that has a single value for each entity, each possible valueneeds to be stored just once per entity in the property's predefined index. Evenso, it is possible for an entity with a large number of such single-valuedproperties to exceed the index entry or size limit. Similarly, an entity thatcan have multiple values for the same property requires a separate index entryfor each value; again, if the number of possible values is large, such an entitycan exceed the entry limit.

The situation becomes worse in the case of entities with multiple properties,each of which can take on multiple values. To accommodate such an entity, theindex must include an entry for every possiblecombination of property values.Custom indexes that refer to multiple properties, each with multiple values, can"explode" combinatorially, requiring large numbers of entries for anentity with only a relatively small number of possible property values. Suchexploding indexes can dramatically increase the cost of writing an entity toDatastore, because of the large number of index entries that mustbe updated, and also can easily cause the entity to exceed the index entry orsize limit.

Consider the query

SELECT*FROMWidgetWHEREx=1ANDy=2ORDERBYdate

which causes the SDK to suggest the following index:

indexes:-kind:Widgetproperties:-name:x-name:y-name:date

classWidget(db.Expando):passe2=Widget()e2.x=[1,2,3,4]e2.y=['red','green','blue']e2.date=datetime.datetime.now()e2.put()

creates an entity with four values for propertyx, three values for propertyy, anddate set to the current date. This will require 12 index entries, onefor each possible combination of property values:

(1,"red",<now>)(1,"green",<now>)(1,"blue",<now>)

(2,"red",<now>)(2,"green",<now>)(2,"blue",<now>)

(3,"red",<now>)(3,"green",<now>)(3,"blue",<now>)

(4,"red",<now>)(4,"green",<now>)(4,"blue",<now>)

When the same property is repeated multiple times, Datastore candetect exploding indexes and suggest an alternative index. However, in all othercircumstances (such as the query defined in this example),Datastore will generate an exploding index. In this case, you cancircumvent the exploding index by manually configuring an index in your indexconfiguration file:

indexes:-kind:Widgetproperties:-name:x-name:date-kind:Widgetproperties:-name:y-name:date

(1,<now>)(2,<now>)(3,<now>)(4,<now>)

("red",<now>)("green",<now>)("blue",<now>)

Any put operation that would cause an index to exceed the index entry or sizelimit will fail with aBadRequestErrorexception. The text of theexception describes which limit wasexceeded ("Too many indexed properties" or"Index entries too large") andwhich custom index was the cause. If you create a new index that would exceedthe limits for any entity when built, queries against the index will fail andthe index will appear in theError state in the Google Cloud console. Toresolve indexes in theError state:

1. Remove the index in theError state from yourindex.yaml file.

2. Run the following command from the directory where yourindex.yaml islocated to remove that index from Datastore:

   gclouddatastoreindexescleanupindex.yaml

3. Resolve the cause of the error. For example:

   • Reformulate the index definition and corresponding queries.
   • Remove the entities that are causing the index to explode.

4. Add the index back to yourindex.yaml file.

5. Run the following command from the directory where yourindex.yaml islocated to create the index in Datastore:

   gclouddatastoreindexescreateindex.yaml

You can avoid exploding indexes by avoiding queries that would require a customindex using a list property. As described above, this includes queries withmultiple sort orders or queries with a mix of equality and inequality filters.