Mirroring ODBC data source to MySql

Question

I have a data source on a Windows machine that supports an ODBC connection. I want to programmatically copy that entire data source to a MySql database every hour. Is there something faster than doing a select * from and then doing an insert for each row?

Answer 1

You probably can't avoid the select * from but you can make it significantly faster (depending on the size of your tables and number of indexed columns) by disabling keys on the destination table before the copy and re-enabling them after. Here's the doc.

Answer 2

Dont do the insert for each row. Each one takes extra time for the DB to respond that each row was successful. Instead, create batches of 100 inserts at a time with SQL transactions...

Answer 3

The select * from couldn't be avoided, as mentioned.

However, the inserts were two orders of magnitude faster than other methods if all the data was written to a file and LOAD DATA INFILE ... was used.

The inserts were quick enough to cover the costs of writing ODBC data to a file and deleting the MySql data completely and starting from scratch.