MySQL Training Summary

[size=large][b]Store Engine[/b][/size]
In a database, different tables can use different store engines.
[list=1]
[*] MyISAM
Not Support: Transaction, Row level locking
Support: Full-text index
So MyISAM is good at big amount data query.
[*] 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.
[/list]
[size=large][b]Configuration[/b][/size]
[list=1]
[*] show variables : Display static parameters
[*] show status : Display dynamic parameters
[/list]
[size=large][b]Storage[/b][/size]
[list=1]
[*] MySQL data default location is /usr/local/mysql/data. Use an other partition store data. To avoid the disk operation competition.
[*] *.myd is MyISAM data file. *.myi is MyISAM index file.
[*] Data, index and log files should not store in the root partition, since the performance reason.
[*] Store data files and index files separately. (InnoDB cannot do that)
[/list]
[size=large][b]Partitioning (Horizon)[/b][/size]
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;