in reply to [untitled node, ID 280779]

Since this is web based and moderately trafficked, efficiency and scalability is important to avoid repeatedly SELECTing an ever growing number of records. ... I want to do this the Right Way™, and avoid all unnecessary server load.

dragonchild gave you the right answer from the perspective of having a normalized schema, but didn't discuss performance.

If you have a Group table, indexed by group ID, and a linking table indexed by user ID + group ID, the JOIN you need to get from a user ID to a list of group names will scale fairly well. Because you have indexes, you're not scanning entire tables. Because of the composite index on the linking table, MySQL can do a partial key lookup (which avoids scanning the entire table), and can pull the group ID out of the index. From there, the lookup of the corresponding Group record is done using an index.

If you're skeptical, as you should be if you're concerned about scalability, prototype this scheme and benchmark it.

  • Comment on Re: MySQL / Perl theory & design conundrum

Replies are listed 'Best First'.
Re: Re: MySQL / Perl theory & design conundrum
by shemp (Deacon) on Aug 04, 2003 at 21:47 UTC
    If the number of records gets big, you may need a second index on the linking table. Having the index (userID, groupID) as a primary key will indeed keep out duplicate records, and allow mysql to quickly answer the question of what all groups is user X in, because querying with a known userID, mysql can use the left column of the combined index.

    But, if you want to know all the users in a given group, the linking table does NOT have an index to look up groups with. (userID, groupdID) does not contain a leftmost prefix (groupID). A table scan will be performed here. So an index on the groupID may be useful.

    If your record count stays small, i'd go with dragonchilds index only. But if the numbers get big, the second index will become useful.
[reply]

In Section Seekers of Perl Wisdom