Having recently significantly exceeded the 200 index limit... I began trying to figure out what GAE actually supports in a desperate attempt to get back under the limit. You'd think the documentation would make this clear and maybe it does for bigger brains than mine. But for me some of the older articles and videos just muddy the water and make it unclear what's actually real today vs wishful thinking from the past. The 2011 Alfred Fuller article does a pretty good job of getting you on the right track (https://developers.google.com/appengine/articles/indexselection). The Zigzag merge join that Alfred describes seems to be real and available today. However, there is one limitation that was unclear to me and I summarize my understanding as follows:
*GAE can use any combination of indexes to satisfy a query AS LONG AS the index contains no outsiders.* In other words... every field in an index MUST be in the filter expression or the index will NOT be used!!! Stated yet another way... GAE does NOT use more specific indexes to satisfy less specific queries. For example, Index(Model, field1, field2, fieldSort) Can NOT be used with: SELECT * from Model WHERE field1=val1 ORDER BY fieldSort SELECT * from Model WHERE field2=val2 ORDER BY fieldSort Because the index contains fields that are NOT in the filter expression of each query Given, Index(Model, field1, field2, fieldSort) Index(Model, field3, field4, fieldSort) CAN be used with: SELECT * from Model WHERE field1=val1 AND field2=val2 ORDER BY fieldSort SELECT * from Model WHERE field3=val3 AND field4=val4 ORDER BY fieldSort SELECT * from Model WHERE field1=val1 AND field2=val2 AND field3=val3 AND field4=val4 ORDER BY fieldSort Can NOT be used with: SELECT * from Model WHERE field1=val1 AND field2=val2 AND field3=val3 ORDER BY fieldSort (or any other combination of 3 fields) BECAUSE the second index contains field4 (an outsider) which is NOT in the filter expression! (and field3 isn't available in any other index) Indexing each field individually as Alfred discusses works for any combination of fields 1-4 with fieldSort because GAE can always find a set of indexes that contain no outsiders. It's really as simple as that! Overlap is OK but outsiders are NOT.... Index(Model, field1, field2, field3, fieldSort) Index(Model, field2, field3, field4, fieldSort) CAN be used with: SELECT * from Model WHERE field1=val1 AND field2=val2 AND field3=val3 AND field4=val4 ORDER BY fieldSort The overlap of fields 2,3 in the indexes are OK and there are no outsiders in either index However, now the following queries DON''T WORK because there are outsiders in the available indexes: SELECT * from Model WHERE field1=val1 AND field2=val2 ORDER BY fieldSort (field3 is an outsider) SELECT * from Model WHERE field3=val3 AND field4=val4 ORDER BY fieldSort (field2 is an outsider) Anyway... I just thought I would share my insight FWIW I still don't know if I can make the 200 index limit but at least now *I think* I understand what works and what doesn't. :) -- You received this message because you are subscribed to the Google Groups "Google App Engine" group. To unsubscribe from this group and stop receiving emails from it, send an email to google-appengine+unsubscr...@googlegroups.com. To post to this group, send email to google-appengine@googlegroups.com. Visit this group at http://groups.google.com/group/google-appengine. For more options, visit https://groups.google.com/d/optout.
