I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database.

For this I need to import an IP location CSV (country IP address to city) into my local MySQL database*, but it's taking a while:

enter image description here

I started importing the data roughly 40 minutes ago, and it looks like it'll take a few hours.

Is there a faster way of importing the data. I'm currently using MySQL Workbench and it uses no more than 15% of my CPU (using an Intel Xeon X5650 with 16GB RAM on Windows 10 Pro x64), so the issue may be that MySQL Workbench simply isn't optimized for a data import of this scale.

Is there a faster way of importing this data?

*Will be Amazon Aurora when I have the money to do so, so I'm not worried about the query speed.

  • Try disabling indexes and parallel insertion possibly using some script. – Saleem Jun 22 '16 at 21:53
  • Looks like an X-Y problem. Instead of rolling your own, consider using an existing service, it will save you not only loading time but also development and maintenance efforts. – mustaccio Jun 23 '16 at 1:12
  • the load data infile statement should be optimized for this. No idea if WB uses transactions properly (if your table is even InnoDB) etc. – jkavalik Jun 24 '16 at 21:26
  • I/O delay? Ethernet delay? Low value of innodb_buffer_pool_size? It should be about 11G for your system. – Rick James Jul 3 '16 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.