MySQL Forums
Forum List  »  MyISAM

Re: Solving table locking issues
Posted by: Steve Gurasich
Date: February 18, 2009 01:25PM

Yeah, that was pretty stupid to do, uid and username are 1:1. I have another query in that same script to get the actual user records:

SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, IF(`instructor`, 'Instructor', 'Student') AS `type`, 'Auto' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id})
UNION
SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, IF(`instructor`, 'Instructor', 'Student') AS `type`, 'Manual' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id})
UNION
SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, 'UG Admin' AS `type`, 'Manual' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id})
ORDER BY {$sort}

In order to get a count of them (there's actually a LIMIT on the above), I just wrapped a count around that without thinking too hard about the query, but you're right, it doesn't need to select from the users table at all.

The autoassigned_users and usergroup_users tables might have the same uid, but the usergroup_admins should not share IDs with the other tables. All 3 tables are just 2-column tables with 2 ints. I believe I do need to use UNION ALL though, because I do want to show both records if the same user was both auto and manually assigned. So that should simplify the count quite a bit:

  SELECT COUNT(*) AS `num` FROM (
  SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id}
  UNION ALL
  SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id}
  UNION ALL
  SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id}
  ) AS u

This is the complete PHP script to get all users in a usergroup. This system is using AJAX with the UI, so this script just gets included from the global I/O script and gets the data for the JSON response to the AJAX request. Things like $page_mode, $db, and $sess are defined in the global I/O script.

<?php
if (!$sess->any_admin)
{
  $response['errors']['reason'] = 'You do not have permission.  Your log-in session might have expired.';
  return;
}

$sort = form_var('sort', 'username');

$cache_id = substr('page='.$page_mode.'&id='.$id, 0, 200);
$cached = $cache->lookup($cache_id);
if ($cached !== false)
  $response['totalCount'] = $cached;
else
{
  $sql_count = "
  SELECT COUNT(*) AS `num` FROM (
  SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id}
  UNION ALL
  SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id}
  UNION ALL
  SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id}
  ) AS u
  ";
  $db->sql($sql_count);
  $num = $db->select();

  $response['totalCount'] = $num[0]['num'];
  $cache->save($cache_id, $response['totalCount']);
}

$sql = "
SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, IF(`instructor`, 'Instructor', 'Student') AS `type`, 'Auto' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id})
UNION ALL
SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, IF(`instructor`, 'Instructor', 'Student') AS `type`, 'Manual' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id})
UNION ALL
SELECT `username`, `fname`, `lname`, IF(`active`, 'Yes', 'No') AS `active`, 'UG Admin' AS `type`, 'Manual' AS `method` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id})
ORDER BY {$sort}
";

$db->sql($sql);
$response['data'] = $db->select($limit, floor($start / $limit) + 1);

$response['success'] = true;

?>

To clarify, there is a single 'users' table that lists all user accounts. The other 3 tables I'm using here just link users to usergroups (that relationship is many-to-many). Users can either be automatically or manually assigned to a usergroup, and may be both, but we need to keep track of which type of assignment it is (hence the two tables), and user group admins get their own table as well. That could probably be simplified with a single assignment table which includes a column for auto/manual and admin status, although the admin status for a single user (user type) is stored in the regular users table.

This system is a rewrite from a previous version that had a setup like this, and it made sense, but when I look at it from an outside view with your help obviously there are several issues like this that I just overlooked when designing the new system. It would definitely be more efficient to have a single table to list all user/usergroup assignments, how they got there, and what type of user they are for the group. I can't think of any advantages of using 3 tables to do that job.

Quote

An "entity" table that contains one row per user, plus one row per group. Content can then be assigned to an "entity".
That's basically what the content assignment table is (called assignment_cache). At the end of the day, it's just content being assigned to users. But we need to know how they got those assignments, and it could come from 4 places. The content could be directly assigned to the user, or assigned to a user group that the user is a member of, or an entire content group that contains the content could be assigned to a single user or entire user group. So the assignment_cache table just lists user IDs and content IDs, but the other 4 tables for content assignment keep track of how they got that content. When the system looks up who gets what, it only looks in the assignment_cache table though. The old system was joining up the other 4 tables, but that was taking too long. When an admin changes content assignments, such as assigning a new content group to a new user group, it will clear out the assignment cache for the users in that group and recalculate everything from the 4 content assignment tables.

Options: ReplyQuote


Subject
Views
Written By
Posted
8117
February 10, 2009 01:29PM
3524
February 14, 2009 02:08AM
3671
February 17, 2009 03:30PM
2885
February 18, 2009 12:38AM
Re: Solving table locking issues
3480
February 18, 2009 01:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.