MySQL Training Summary

Store Engine
In a database, different tables can use different store engines.

1. MyISAM
Not Support: Transaction, Row level locking
Support: Full-text index
So MyISAM is good at big amount data query.
2. InnoDB (Default store engine from MySQL 5.5)
Not Support: Full-text index
Support: Transaction, Row level locking
InnoDB is good at DML operations (Delete, insert, update). For most of cases, use InnoDB is better choice.

Configuration

1. show variables : Display static parameters
2. show status : Display dynamic parameters

Storage

1. MySQL data default location is /usr/local/mysql/data. Use an other partition store data. To avoid the disk operation competition.
2. *.myd is MyISAM data file. *.myi is MyISAM index file.
3. Data, index and log files should not store in the root partition, since the performance reason.
4. Store data files and index files separately. (InnoDB cannot do that)

Partitioning (Horizon)
MySQL 5.5 has build-in partitioning function.
Range Partitioning:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

HASH Partitioning:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;