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;