Optimizing Schema and Data Types - High Performance MySQL - page 143

mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
+--------+------------------------------------+------+-----+---------+-------+
We'll add a PG-14 rating for parents who are just a little bit more cautious about films:
mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
-> DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;
Notice that we're adding the new value at the end of the list of constants . If we placed
it in the middle, after PG-13, we'd change the meaning of the existing data: existing R
values would become PG-14, NC-17 would become R, and so on.
Now we swap the .frm files from the operating system's command prompt:
/var/lib/mysql/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm
Back in the MySQL prompt, we can now unlock the table and see that the changes took
effect:
mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'\G
*************************** 1. row ***************************
Field: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')
The only thing left to do is drop the table we created to help with the operation:
mysql> DROP TABLE sakila.film_new;
Building MyISAM Indexes Quickly
The usual trick for loading MyISAM tables efficiently is to disable keys, load the data,
and reenable the keys:
mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load the data
mysql> ALTER TABLE test.load_data ENABLE KEYS;
This works because it lets MyISAM delay building the keys until all the data is loaded,
at which point it can build the indexes by sorting. This is much faster and results in a
defragmented, compact index tree. 9
Unfortunately, it doesn't work for unique indexes, because DISABLE KEYS applies only
to nonunique indexes. MyISAM builds unique indexes in memory and checks the
9. MyISAM will also build indexes by sorting when you use LOAD DATA INFILE and the table is empty.