One of the ways to import data into MySQL is using the LOAD DATA INFILE. It is a faster method than recovering from a dump, as it’s raw data instead of SQL sentences.
The import time depends on the table engine, for example, MyISAM can be 40 times faster than Innodb. Let’s benchmark this:
I’m gonna make some benchmarking using MySQL 5.1.36 (64 bits MacOS X). I’ll need a big table, so I’ll take City from the World Database and create a huge table called “city_huge”:
CREATE TABLE city_huge LIKE CITY; INSERT INTO city_huge SELECT NULL, name, CountryCode, District, Population FROM city; # Run this sentence 100 times, # so city_huge table will be 100 times bigger than city. # Tip: use a script, temporary table,[Read more...]