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