MySQL is a popular Open Source database, commonly available on web servers. Earlier versions were lacking in some features, but it has now proven suitable for large applications and websites.

Storage Engine

Pick InnoDB if you need row level transactions. MyISAM is another storage engine, but it doesn’t support row level locking, only table level locking.

Syntax

On linux the table and column names are case-sensitive. This can cause issues with migrating a MySQL database between different servers.

Alter table keys

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

Create user

create user example_user@localhost identified by 'password';
create user example_user@'%' identified by 'password';

% is a wildcard for ANY host.

example_user is the username.

password is the password.

Create database

create database dbtest
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

The utf8 character set is flexible as it will accommodate internationalization.

Grant

mysql> grant all on db_test.* to test_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Allows test_user to connect from anywhere and to access the db_test database in full.

innodb_file_per_table

Each table will have its own tablespace instead of using a common tablespace. On the file system, this means each database table with have its own file on disk. This is useful if you are low on space - you can drop a table and disk space will be reclaimed. This is not possible when you are using a common tablespace. 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 under the mysqld section.

To reclaim space you can run optimize table table_name. This will rebuild the corresponding ibd file. The table will be locked for the duration of the command. While the command is running, a new temporary table is being created with name prefixed with #sql- (e.g. “#sql-50e7_5.ibd”). You can see the size growing on disk, so you can compare this to the original table to get an idea of progress.

+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| Example.example    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| Example.example    | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+

Scripting

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

The MySQL Query Cache

The command show global status will give you a status snapshot of the server. See server status variables docs

Variables related to 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.

Calculate the hit percentage as Qcache_hits / (Qcache_hits + Com_select)

Slow subselect or nested select statements

As of version 4 and 5, MySQL nested selects are not optimized as you might expect. You must understand the order in which it calculates the results. Consider the following:

select * from Snake
where Snake.id in (select id from Venomous)

mysql> explain select * from Snake where Snake.id in (select id from Venomous);
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table    | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | Snake    | index           | NULL          | PRIMARY | 4       | NULL |   20 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | Venomous | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index              |
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+

MySQL will look at the Snake table first, and attempt to join each row with the Venomous table.

Intuitively you may think it will initially calculate select id from Venomous and determine a list of ids, then join with the Snake table, but this is not the case.

If the snake table is huge and you are only expecting a handful of Venomous records, you should rewrite the query as

select s.* from Snake s,
Venomous v
where s.id = v.id;

mysql> explain select s.* from Snake s,
    -> Venomous v
    -> where s.id = v.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | v     | index  | PRIMARY       | PRIMARY | 4       | NULL        |    2 | Using index |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY       | PRIMARY | 4       | v.id        |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)

This allows the optimizer to decide which table to read first.

A DELETE statement with subselect or nested select will suffer from the same issue. You can rewrite

delete from Snake
where id in (select id from Venomous);

as

delete s from Snake s, Venomous v
where v.id = s.id;

As usual, benchmark your statements to see what works best on your data and MySQL version.