Hi Martin, I think the index statistics are not stale. However, I just found out from our system admin that currently we still have mdl_user table using MyISAM storage engine (and all other tables had been converted to InnoDB a while ago). Not sure why our system admin wanted to have it this way and whether this would have any impact on the performance of this specific query. We will convert this last table (mdl_user) to InnoDB this coming Sunday and we will see if this make any differences.
By the way, for larger sites, specifically on this "Assign roles" page, The element on the right side will have too many users to show anyway, and will just display:
"There are too many users to show. Try searching instead."
In this case (large sites), it doesn't make sense to still run this query then as the query result will not be displayed anyway (unless that I missed something here). Am I correct on this? If I'm correct, then maybe we should add a programming logic on this page - if too many users (need to use "search" instead), then skip this query. What do people think? Thoughts or comments? Thanks!