Kenneth at 22:56, 28 March 2016

2016-03-28T22:56:48Z

New page

Tables:
<pre>
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 | |
+-------+---------------------+------+-----+---------+----------------+
</pre>

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:
<pre>
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
</pre>

Fill Groups:
<pre>
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))
</pre>

DB Create:
<pre>
$ 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`)
);
</pre>

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;

MySQL/Test - Revision history

Kenneth at 22:56, 28 March 2016