Google Datastore error: No matching index found

Question

I want to test a new Entity I created in the Google Datastore. I'm trying to do a GQL Query with an inequality to retrieve some entities in the datastore web interface:

SELECT * FROM UserMatchingIndex WHERE age < 25 AND wants_male = false AND wants_dating = false AND wants_friendship = false AND wants_age = 20

But I always have an error : "GQL query error: Your Datastore does not have the composite index (developer-supplied) required for this query." whereas I have defined the required composite indexes !

UserMatchingIndex: age ▲ wants_male ▲ wants_dating ▲ wants_friendship ▲ wants_age ▲ Serving

UserMatchingIndex: age ▲ wants_female ▲ wants_dating ▲ wants_friendship ▲ wants_age ▲ Serving

These are defined as followed in the index.yaml:

- kind: UserMatchingIndex
  ancestor: no
  properties:
  - name: age
  - name: wants_male
  - name: wants_dating
  - name: wants_friendship
  - name: wants_age

- kind: UserMatchingIndex
  ancestor: no
  properties:
  - name: age
  - name: wants_female
  - name: wants_dating
  - name: wants_friendship
  - name: wants_age

I really don't see what could be possibly wrong... I've done that many times for other entities. If you have some clues, you're welcome.

Do these indexes have a "Serving" status in the dev console? — Andrei Volgin
Normally the error in the log suggests what index should be added. At least that's what it does if you'd use UserMatchingIndex.query().filter(UserMatchingIndex.wants_male == False, UserMatchingIndex.wants_dating == False, UserMatchingIndex.wants_friendship == False, UserMatchingIndex.wants_age == 20, UserMatchingIndex.age < 25) — Vincent
I'm using PHP App Engine, I don't have any more info than "No matching index found" as an error. — Manfred Von Karma

Manfred Von Karma Manfred Von Karma · Accepted Answer · 2016-04-22T08:48:08

This issue was submitted to Google Cloud support and it seems to be an issue on their side, or at least, a restriction not documented yet. Changing the order of the properties as followed:

- kind: UserMatchingIndex
  ancestor: no
  properties:
  - name: wants_male
  - name: wants_dating
  - name: wants_friendship
  - name: wants_age
  - name: age

makes the query work.

EDIT: Answer from Google Support

"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:

Properties used in equality filters

Property used in an inequality filter (of which there can be no more than one)

Properties used in sort orders

This ensures that all results for every possible execution of the query appear in consecutive rows of the table."

This restriction should be documented soon.

Google Datastore error: No matching index found

1 Answers