Move million records from MEMORY table to MYISAM table

Question

I am looking for a fast way to move records from a MEMORY table to MYISAM table. MEMORY table has around 0.5 million records. Both tables have exactly the same structure (same number of columns, data types etc.). But the MYISAM table is indexed (B-TREE) on a few columns. There are around 25 columns most of which are unsigned integers.

I have already tried using "INSERT INTO SELECT * FROM " query. But is there any faster way to do this?

Appreciate your help.

Prashant

Answer 1

A others pointed out -- you should not use indexes during insert. You can disable updating them on every insert:

ALTER TABLE table DISABLE KEYS;
INSERT INTO table
ALTER TABLE tbl_name ENABLE KEYS;

And also lock a table to get single index write:

LOCK TABLES table WRITE;
INSERT INTO table
UNLOCK TABLES;

Anyway, if you use it in a single INSERT ... SELECT you might not get significant performance gain.

You can also tune bulk_insert_buffer_size setting in the server config.

More on: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html