Queries for permission checks are slow in large sites

Permission checks like the one below turn out to be slow in sites with large membership. For example in one site with 22300 members, this query appears to scan 67544 rows, even though a mysql 'explain' would seem to suggest that the query is using indexes correctly and is relatively efficient.

A large number of such permission checks in a short space of time have in the past caused major performance problems for the database.

1. Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 67544
   select count(1) from SAKAI_REALM_RL_FN MAINTABLE LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY =
   GRANTED_ROLES.REALM_KEY AND MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS where ( ROLES.ROLE_NAME in('.anon','.auth') or ( GRANTED_ROLES.USER_ID = 'xyz' AND GRANTED_ROLES.ACTIVE = 1 ) ) AND FUNCTIONS.FUNCTION_NAME = 'calendar.all.groups' AND REALMS.REALM_ID in ('/site/96dbcba6-01c2-4059-007a-40e484873bb9') AND MAINTABLE.REALM_KEY
   = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY;