Appengine Datastore, one index for multiple queries

Question

I have several queries using more than one property in my code. For example, suppose one ndb.Model called user

And some queries:

user.query(user.enabled == Ture, user.name == "demo").order(user.age).fetch(limit=10)
user.query(user.enabled == True, user.domain == "demo.com").fetch(limit=10)
user.query(user.enabled == False, user.phone == 2).fetch(limit=10)

I want to use only one index (containing all fields) in order to reduce a large set. (Google limits to 200)

- kind: user
  properties:
  - name: enabled
  - name: name
  - name: age
  - name: domain
  - name: phone

But is not working.

If it's impossible, is there any way to increase the index limit?

Thx, sorry for my English.

I'm not sure if i understand.

Here are a real sample.

class dbRealEstateImages(ndb.Model):
    realestate = ndb.IntegerProperty(required=True)
    md5 = ndb.StringProperty()
    image_file = ndb.BlobKeyProperty()
    image_url = ndb.StringProperty()
    position = ndb.IntegerProperty(default=0)

And I want this query:

dbRealEstateImages.query(dbRealEstateImages.realestate == reID).order(dbRealEstateImages.position).fetch(limit=None, projection=[dbRealEstateImages.image_url])

We have 3 properties, one for filter, other for sorting, and another one as a projection.

So we need one index for property and sort order, based on the documentation, could be something like this:

Index(dbRealestateImage, realestate, position)
Index(dbRealestateImage, image_url, position)

But, in ascending order, we don't need to include into the index, so:

Index(dbRealestateImage, realestate)
Index(dbRealestateImage, image_url)

If we have only one property, we don't need to index, so i think, "this query don't need indexes"

But appengine raises

no matching index found.
The suggested index for this query is:
- kind: dbRealEstateImages
  properties:
  - name: realestate
  - name: position
  - name: image_url

So, I'm a little bit counfused :(

P.D.: In my quota details, all the index are count, autogenerated, included.

Hanuman Hanuman · Accepted Answer · 2014-08-30T22:08:03

You can't use only one index, they have to be really specific to the query in order to be fast. However for most of your example queries automatic indexes will work fine, thanks to the zigzag merge join algorithm, and those don't count towards your 200 index limit.

Sorting will require a custom index, but only per property, for example:

Index(User, domain, -age)
Index(User, enabled, -age)
Index(User, name, -age)
Index(User, phone, -age)

This 4 indexes should enable you to use any query that orders by age, no matter what properties are you filtering by (if you were to add composite indexes for every permutation you would need 16).

The only caveat is that the shape of your data will dictate how efficiently the algorithm runs; it is very smart, but you should read the performance section of the docs so you know how to deal with the complex cases.

As always, you should first measure; when you detect that a common query is not performing as fast as you need it, then you can add a composite index for that specific case.

I highly recommend reading the Index Selection and Advanced Search document, which covers all this information in great detail.

As for raising the 200 limit, it can be done as a Premier customer if you contact support (never done that so can't speak about how feasible it is), but I highly recommend you to optimize your index usage first; having lots of them is easy, but it also gets really expensive, so it is worth it to spend the time thinking about your best strategy (I have always found I didn't really needed as many).

Appengine Datastore, one index for multiple queries

1 Answers