MySQL Forums
Forum List  »  MyISAM

Re: Solving table locking issues
Posted by: Rick James
Date: February 18, 2009 12:38AM

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".

Options: ReplyQuote


Subject
Views
Written By
Posted
7625
February 10, 2009 01:29PM
3383
February 14, 2009 02:08AM
3462
February 17, 2009 03:30PM
Re: Solving table locking issues
2710
February 18, 2009 12:38AM
3277
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.