I am using the 64-bit version of MySQL 5.5 on a Windows 7 machine.
I am trying to import some very large geospatial data files from geonames.org into MySQL so that I can run queries against it and generate a smaller set of data for my purposes.
The storage engine I am using is MyISAM and the input data is a 1 gigabyte text file with each row separated by tabs. I believe the text file contains about 8 million rows.
Initially, I imported the file into a database with the InnoDB engine. There were a lot of warnings, probably due to the geospatial data. The import took about 6.5 minutes, which was quite reasonable.
I then tried to import the data into a database using the MyISAM storage engine. The import is still running and I have spent more than 30 minutes waiting for the import to finish.
The server is configured with the stock "Developer" settings. I noticed that when I look at server status using MySQL Workbench, the Key efficiency runs at 100%, while the memory usage sits at about 40% (I have 12GB of RAM in the machine) and CPU usage sits at about 5%. Are there any particular settings/tweaks I should look at to speed up the import?
EDIT: So it just finished importing: Are there any ways to speed this up?
Query OK, 7836651 rows affected, 65535 warnings (50 min 20.89 sec)
Records: 7836651 Deleted: 0 Skipped: 0 Warnings: 5630783
It sounds like the way to go is to use InnoDB. Also tune the relevant MySQL settings to use more RAM if you want, and get faster disks (if CPU and memory aren't pegged, then the disks are almost certainly the bottleneck).
If you were using LOAD DATA INFILE, you need to make sure you increase bulk_insert_buffer_size to something significant like 256M.