MySQL/Test

Tables:

mysql> describe users;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| uid      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   | UNI | NULL    |                |
| name     | varchar(40)      | NO   |     | NULL    |                |
| phone    | varchar(20)      | NO   |     | NULL    |                |
| address  | varchar(100)     | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

mysql> describe groups;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| gid       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| groupname | varchar(20)      | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

mysql> describe user_groups;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| uid   | int(10) unsigned    | NO   | MUL | NULL    |                |
| gid   | int(10) unsigned    | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

Fill Users:

insert into groups (gid, groupname) values (1, 'Admins');
insert into groups (gid, groupname) values (2, 'Users');
insert into groups (gid, groupname) values (3, 'IT');
insert into groups (gid, groupname) values (4, 'HR');
insert into groups (gid, groupname) values (5, 'Something Else');
insert into groups (gid, groupname) values (6, 'Not Sure');

Fill Users:

import _mysql
import random
import string

DBHOST='XXX'
DBUSER='XXX'
DBPASSWORD='XXX'
DBDB='XXX'

def rstr(N):
  N=random.randint(1, N)
  return ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(N))

db=_mysql.connect(host=DBHOST,user=DBUSER,
                  passwd=DBPASSWORD,db=DBDB)

i = 1
while True:
  #db.query("""insert into users (username, name, phone, address) values ('username', 'name', 'phone', 'address');""")
  #db.errno()
  db.query("""insert into users (username, name, phone, address) values ('%s', '%s', '%s', '%s');""" % (rstr(10), rstr(20), rstr(10), rstr(30)))
  i = i + 1
  print i

Fill Groups:

import _mysql
import random

DBHOST='XXX'
DBUSER='XXX'
DBPASSWORD='XXX'
DBDB='XXX'

db=_mysql.connect(host=DBHOST,user=DBUSER,
                  passwd=DBPASSWORD,db=DBDB)
db2=_mysql.connect(host=DBHOST,user=DBUSER,
                  passwd=DBPASSWORD,db=DBDB)
db.query('select uid from users');
r=db.use_result()
while True:
  uid=r.fetch_row()[0][0]
  print uid
  for i in range(1, 6):
    gid=random.randint(1,6)
    db2.query("""insert into user_groups (uid, gid) values (%s, %s);""" % (uid, gid))

DB Create:

$ mysqldump -h HOST -u USER -pPASSWORD DBDB --no-data --compact    # cleaned up...
CREATE TABLE `groups` (
  `gid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `groupname` varchar(20) NOT NULL,
  PRIMARY KEY (`gid`)
);

CREATE TABLE `user_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `gid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`,`gid`)
);

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `username` (`username`)
);

Show indexes in mysql:

mysql> show index from users.users;
mysql> show index from users.user_groups;
mysql> show index from users.groups;

Test speed:

mysql> select * from users, groups, user_groups where username = '001Bgh1GTZ7hW3O' and user_groups.gid = groups.gid and users.uid = user_groups.uid;

mysql> select * from users, groups, user_groups where username = (select uid from users where id=1000001) and user_groups.gid = groups.gid and users.uid = user_groups.uid;