MySQL

Very popular Open Source database, commonly available on web servers. Lacking in some features, it is suitable for small applications and websites.

1. Syntax

Alter table keys

alter table Airline add unique key code (code);
alter table Airline drop key code;

2. innodb_file_per_table

Each table will have its own tablespace instead of using a common tablespace. This is useful if you are low on space - you can drop at table and disk space will be reclaimed. If you are using a common tablespace then its not possible. Usually database servers are allocated space beforehand, so this is more of a developer setup. If you were to do this on production I'm unsure of the performance impacts.

Add "innodb_file_per_table" to my.ini on a line by itself.

3. Scripting

mysql client password can be communicated by exporting the MYSQL_PWD environment variable.

4. The MySQL Query Cache

Docs at The MySQL Query Cache.

The command "show global status" will give you a snapshot of the server. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html

Variables to do with the query cache are

Qcache_lowmem_prunes may indicate that your cache is too small.

If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. If it misses the cache and has to evaluate the select statement, it will increment Com_select.

I calculate the hit percentage as Qcache_hits / (Qcache_hits + Com_select)