User accounts are passworded. When connecting to the database the password is encrypted to assure that it can not be intercepted and used by others.

The mysql database contains three tables. They are defined below.

Database: mysql Table: db

Field	Type	Null	Key	Default	Extra
host	char(60)		PRI
db	char(32)		PRI
user	char(16)		PRI
select_priv	char(1)			N
insert_priv	char(1)			N
update_priv	char(1)			N
delete_priv	char(1)			N
create_priv	char(1)			N
drop_priv	char(1)			N
file_priv	char(1)			N

Database: mysql Table: host

Field	Type	Null	Key	Default	Extra
host	char(60)		PRI
db	char(32)		PRI
select_priv	char(1)			N
insert_priv	char(1)			N
update_priv	char(1)			N
delete_priv	char(1)			N
create_priv	char(1)			N
drop_priv	char(1)			N

Database: mysql Table: user

Field	Type	Null	Key	Default	Extra
host	char(60)		PRI
user	char(16)		PRI
password	char(8)
select_priv	char(1)			N
insert_priv	char(1)			N
update_priv	char(1)			N
delete_priv	char(1)			N
create_priv	char(1)			N
drop_priv	char(1)			N
reload_priv	char(1)			N
shutdown_priv	char(1)			N
process_priv	char(1)			N

The following is an example of adding new users.

$ mysql mysql mysql> INSERT INTO user VALUES ('%','monty',password('something'), -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user (host,user,password) values('localhost','dummy','') ; mysql> INSERT INTO user VALUES ('%','admin','','N','N','N','N','N','N','Y','N','Y','N') ; mysql> quit $ mysqladmin reload

This makes three new users:

monty:	Full superuser, but must use password when using mysql.
admin:	Doesn't need a password but is only allowed to use 'mysqladmin reload' and 'mysqladmin processlist'. May be granted individual database privileges through table 'db'.
dummy:	Must be granted individual database privileges through table 'db'.

NOTE: You MUST use the password() function when creating a user that has a password. The mysql database system expects passwords to be stored encrypted.

Attribute assigned in the user table over-ride attribute assigned to a user in a given DB. If you are paranoid or have a server that provides multiple databases you probably want to create users in the user account with no privileges, and then assign appropriate privileges on a database by database basis.

• A host, user and db may be given with the SQL REGEXP chars % and _.
• A host may be localhost, a hostname, IP or SQL expression. An empty host entry means any host.
• A db is a database or a SQL expression.
• A empty username means any user.
• A empty password means that any password will do. The user table is or'ed to the db table. This means that a super users only needs to be in the user table with all privilege-flags set to Y.

• On connection the server sends a random number to the client.
• The client scrambles the password to get what the server has and calculates a number from the random number and the scrambled password. This number is sent to the server.
• The server calculates a number from it's saved password and the random number. If this number is same as the client number the connecting is accepted.

$ mysqld -O

Possible variables to option -O are:
keybuffer             current value: 1048568
max_allowed_packet    current value: 65536
net_buffer_length     current value: 16384
max_connections       current value: 128
table_cache           current value: 64
recordbuffer          current value: 524280
sortbuffer            current value: 2097144

keybuffer	Buffer to hold all recently used keys. A big buffer gives the best performance. With the -Sl switch only one buffer is allocated.
max_allowed_packet	The server connection buffer may be resized up to this if the client gives long commands. The initial buffer is 'net_buffer_length'. One buffer is allocated per connection.
net_buffer_length	Initial size of the connection buffer. One buffer is allocated per connection.
max_connections	The maximum number of connections that mysqld can have open at the same time.
table_cache	Maximum number of tables kept open on the server. Tables are kept open to give better query speed on frequently used tables.
recordbuffer	Buffer used to read records sequentially. One buffer will be allocated per connection.
sortbuffer	Buffer used when sorting. One buffer will be allocated per connection.

Things to Know

• All threads share the same base memory.
• Nothing is memmaped yet (except compressed tables but that's another story). There are plans to add this.
• When starting mysqld one can specify a key buffer. This will buffer all keys in all tables on a FIFO basis. (variable keybuffer)
• Each connection uses some thread space, a stack and a connection buffer (variable net_buffer_length).
• Each request doing a sequential scan over a table allocates a read buffer (variable recordbuffer).
• Each request doing a sort allocates a sortbuffer and one or two temporary files. The maximum extra disk-space needed is (sort_key_length +sizeof(long))*2.
• All joins are done in one pass and the result is saved in a memory hash table. (Now you know how to crash mysql the easy way: Do a full join and a sort on two big tables :)
• Almost all memory used when parsing and calculating is done on a local store which may be freed in a big hunk: No memory overhead is needed for small items and the normal slow memory allocation/freeing is avoided. Memory is only allocated for unexpectedly large strings (This is done with malloc()/free().
• Each index file is opened once and the data file is opened once for each concurrently running thread. For each concurrent thread there is allocated a full table struct and field struct and memory for 3 full column rows. (A BLOB takes 5-8 bytes)
• For each table having BLOB's, a buffer is enlarged dynamically to read in bigger BLOB's. If one scans a table there will be an allocated buffer as big as the biggest BLOB.
• All used tables are saved in a cache and used by FIFO. Normally the cache is 64 tables. If a table has been used by 2 running threads at the same time, there will be 2 entries of the table in the cache.
• A mysqladmin reload closes all tables that are not being used and marks all used tables to be closed when the running thread finishes. This will effectively free most used memory.

Here's a partial list:

• Brace removal (All unnecessary braces are removed)
  ((a AND b) AND c OR (((a AND b) AND (c AND d)) -> (a AND b) OR (a AND b AND c AND d)
• Constant folding
  (a<b AND b=c) AND a=5 -> b>5 AND b=5
• Constant condition removal (Needed because of constant folding)
  (b>=5 AND b=5) OR (b = 6 and 5 = 5) or (B=7 and 5 = 6) ->= B=5 or B=6
• Return zero rows if the select is impossible.
• Find all keys that may be used. Use the key that finds the least records. A key is used for the following cases:
  =, >, >= <, <=, BETWEEN and a LIKE with a character prefix 'something%'
• Remove keys that don't span all 'AND' levels and key_parts that don't have all preceding key_parts specified.
  

    key = 1 or A = 10                             -> NULL  (Can't use key)
    key = 1 or A = 10 and key=2                   -> key = 1 OR key = 2
    key_part_1 = const and key_part_3 = const     -> key_part_1 = const
  

• Read all constant tables
  A constant table is:
  
  1. ) A table with only 1 record.
  2. ) A table which a uses only other const tables and constants on a full unique key.

Optimizing Table Space Usage

You can save a lot of space by using the smaller integer values. For instance, MEDIUMINT is often sufficient.

In general you should choose the smallest data-type you will need if you are concerned with space.

< = client 
> = server
[] is one byte
[2: ] is two bytes packed low byte first
[3: ] is three bytes packed low byte first
[4: ] is four bytes packed low byte first
[string: ] is a null byte terminated string
[length: ] This is a length coded integer. Check
           net_store_length() for this one!
           The main idea is to get most frequent length bytes in 1
           byte but to allow longer data (and NULL)