SELECT COUNT(*) AS `num` FROM (
SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id})
UNION
SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id})
UNION
SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id})
) AS u
If id and username are 1:1, why bother to go to the 'users' table?
Also, why not gather the ids first:
-->
SELECT COUNT(*) AS `num` FROM (
SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id}
UNION DISTINCT
SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id}
UNION DISTINCT
SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id}
) AS u
If there is no overlap of uids between the tables, use
UNION ALL
Then it won't have to make a pass over the data to do "DISTINCT".
Why aren't all the "users" in one table? Maybe I am lost in your structure -- could you do SHOW CREATE TABLE for each of the user tables.
Stored Procedures -- be sure to benchmark them.
Ponder this approach:
An "entity" table that contains one row per user, plus one row per group. Content can then be assigned to an "entity".