Increase query performance of devel_switch_user_list

I see your pain. You seem to have a very large number of users and roles! How many?

The way I read the SQL, going to an inner join will remove user 1 from the result set if s/he does not have any of the listed roles. That's a no-go, of course, and you'll have to look for an alternative solution.

As always, D8 needs to go first.

@salvis I'm not sure if the 8.x branch is affected by this issue.
In 8.x we use entity query for the user list; the query for determine the prioritized users already uses INNER JOIN on roles table:

SELECT base_table.uid AS uid, base_table.uid AS base_table_uid, max(users_field_data_2.access) AS expression
FROM 
{users} base_table
INNER JOIN {users_field_data} users_field_data ON users_field_data.uid = base_table.uid
INNER JOIN {user__roles} user__roles ON user__roles.entity_id = base_table.uid
LEFT JOIN {users_field_data} users_field_data_2 ON users_field_data_2.uid = base_table.uid
WHERE  (users_field_data.uid > '0') AND (users_field_data.status > '0') AND (user__roles.roles_target_id IN  ('administrator')) 
GROUP BY base_table.uid
ORDER BY expression DESC

therefore the query not necessarily include user 1 in the result set if s/he does not have any of the listed roles.
Do we need to open an issue for that?

Thanks for checking the D8 query, willzyx!

It makes sense to create a role to display the block, and give it to user 1, too, but I guess you don't necessarily need to do that. So, yes, I think user 1 should be included in the list — s/he can switch away and should always be able to switch back (if the other user has the permission).

Moving this to D7 which does have the outer join, and postponing until we have a full solution for D8.

Yes, I think so, at the top, IF s/he's not already in the list, and remove the last one, to keep the count correct.

The list is sorted with the most recently used account at the top (or at least close to that). We'll lose that feature, because we can't tell where user 1 should be inserted, but that's a fair trade-off.

However, after thinking about it some more here's another idea: not adding user 1 could actually be a useful feature! If you don't allow entering any user name, the user can switch only among the listed accounts, and if user 1 didn't have the "switch user" permission, s/he would never be listed.

I'm not sure it's waterproof... Any thoughts?

Closing for lack of activity. Feel free to reopen if the issue still exists