When you want to import a large MyISAM table from a file and the table has many indexes, this may help you.
$ mysqladmin flush-tables -u a_db_user -p |
$ sudo myisamchk --keys-used=0 -rq /var/lib/mysql/dbName/tblName |
$ mysql -u your_user_name -p mysql> use your_db_name; mysql> LOAD DATA LOCAL INFILE 'your_data_file.csv' INTO TABLE `your_tbl_name` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES; |
$ sudo myisamchk --key_buffer_size=1024M --sort_buffer_size=1024M -rq /var/lib/mysql/dbName/tblName |
$ mysqladmin flush-tables -u a_db_user -p |
LOAD DATA LOCAL INFILE '/home/xxx/data.csv' INTO TABLE mydb.mytable -- make sure the table name is correct! FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (@id, field_a, field_b, @flag, @filetime) SET id = @id+10000, filetime = FROM_UNIXTIME(@filetime), flag=IFNULL(@flag,0); |
Refer to mysql.com and syntax for more details.
Ubuntu 10.10 |