Array index on ArangoDb

Question

My collection in ArangoDb have objects with this schema (simplified):

{
    "userName": "Billy",
    "email": "[email protected]",
    "logins": [
      {
        "authenticationTokens": [],
        "loginProvider": "Facebook",
        "providerKey": "123",
        "providerDisplayName": null
      }
    ],
    "roles": [],
    "claims": []
}

That's ASPNetCore.Identity implementation on ArangoDb by BorderEast aspnetcore-identity-arangodb

PROBLEM:

To auth using Facebook it uses AQL query

for u in IdentityUser for l in u.logins filter l.loginProvider == "Facebook" && l.providerKey == "123" return u

which works well but does not use any index

Indexes used:
    none

I have tried indices:

db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].loginProvider", "logins[*].providerKey" ] });
db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].loginProvider" ] });
db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].providerKey" ], unique: true });

None of them is used.

Can someone advise how the index should look like for that query?

Pushed new nuget package to support your index. At some point I'll put the queries in a config to make this easier :) — Andrew Grothe
– Andrew Grothe, Commented Oct 30, 2017 at 0:07

Andrew Grothe · Accepted Answer · 2017-10-29 22:58:58Z

2

The issue lies with they query. It needs to be re-written and then it will work. It has to do with using the in comparison operator as discussed in this answer:

"Still the query in 2.8 won't use that index because the array indexes are only used for the IN comparison operator."

So if we change the query we get this:

Query string:
 for u in IdentityUser 
  let l = u.logins[*].loginProvider
  let p = u.logins[*].providerKey
  filter  "google"  in l and "googlekey" in p
  return u

Execution plan:
 Id   NodeType          Est.   Comment
  1   SingletonNode        1   * ROOT
  8   IndexNode            1     - FOR u IN IdentityUser   /* persistent index scan */
  9   CalculationNode      1       - LET #7 = ("googlekey" in u.`logins`[*].`providerKey`)   /* simple expression */   /* collections used: u : IdentityUser */
  6   FilterNode           1       - FILTER #7
  7   ReturnNode           1       - RETURN u

Indexes used:
 By   Type         Collection     Unique   Sparse   Selectivity   Fields                          Ranges
  8   persistent   IdentityUser   false    false            n/a   [ `logins[*].loginProvider` ]   ("google" in u.`logins`[*].`loginProvider`)

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   remove-unnecessary-calculations
  3   use-indexes
  4   remove-filter-covered-by-index

edited Oct 29, 2017 at 22:58

answered Oct 29, 2017 at 18:29

Andrew Grothe

2,3941 gold badge34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Grothe Over a year ago

@Ruslan, as the auther of that repo, I'll update the code and push out a new nuget asap.

Collectives™ on Stack Overflow

Array index on ArangoDb

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related