Move million records from MEMORY table to MYISAM table.

Question

Hi,

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

In principle, you should get good performance by:

1. Create the target table without secondary indexes.

2. Sort the contents of the source table on the target table's primary key.

3. Insert sorted records into target table.

4. Add the secondary indexes one at a time.

Answer 2

It's probably mostly about tuning. Is the MyISAM table initially empty? If so, you can do a few cheats - disable indexes during the load, then enable them (this is NOT a good idea on a non-empty table).

Doing an ORDER BY on a memory table is not a particularly good idea, as they usually use hash indexes, hence cannot do an in-order index scan, so it would introduce an extra filesort(), which is probably bad.

Answer 3

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