How much will computer hardware affect the speed of a "load data local infile" query?

Question

I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has worked very well, and very speedily, up until about 2 weeks ago.

Suddenly, every "rs.open..." line, where I actually send the query to MySQL, took about 4-5 seconds, as opposed to the ~1 second it used to take, which drastically increased the time it took to go through each full file.

I still haven't pinpointed the real problem, but it DID get me thinking: Would better computer hardware appreciably speed up the process? I've got 4gigs of ram and a 3.2 Ghz processor, so my computer isn't terrible, but it COULD be better.

Questions

• Can the speed of MySQL queries be appreciably increased with better hardware, or is it somehow limited by MySQL itself?

Answer 1

I would like to recommend something with regard to LOAD DATA INFILE:

You need to increase bulk_insert_buffer_size (such as 256M) because a special tree structure is made to accommodate the loading of the data. Even with LOAD DATA INFILE, the tree structure is used when loading data into non-empty tables.

According to the MySQL Docs on bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

Answer 2

I seriously doubt you're being CPU bound. This can easily be confirmed or denied by watching top (or process manager? in windows). More ram and faster hard disks are likely the hardware facets you'd want to improve on a DB server.