Configure database indexes for Auth models and relations

[bajtos]

At the moment, the authentication code (ACL, Roles, permissions) is making several relatively complex queries that are not backed by any database indices. This has two negative effects:

• The performance suffers, as the database has perform full scan
• Backends like Redis (and possibly ElasticSearch too?) cannot execute these query at all, see No indexes found for <fieldname> impossible to sort and filter using redis connector  loopbackio/loopback-connector-redis#20.

The patch #2368 proposed a solution that would fix Redis, but it's not clear whether it defines correct indices for SQL databases too.

We should run the auth code via a sample app, investigate the SQL/Redis/MongoDB queries being made by LoopBack and then figure out what are the right set of indices we need to create. Whether we need multiple indices (one per each column) or a single composite index instead.

See also #2368 (comment) and #2849 (comment)

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.