Mysql – How much will computer hardware affect the speed of a “load data local infile” query

MySQL

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?

Best Answer

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.

Related Solutions

Mysql – Enabling LOAD DATA LOCAL INFILE in thesql

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7.

Before taking that kind of time, please run the following:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

It should echo the following:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 'ON';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

If it still says 'OFF', then look deep within the compiler settings to enable it.

If it sets to 'ON', you are OK.

Please note I did not say

SET GLOBAL local_infile = 1;

I said use

SET GLOBAL local_infile = 'ON';

The option local_infile is Boolean, not numeric.

If setting this in my.cnf

[mysqld]
local_infile=ON

and restarting mysql does not work either, you will have to start up mysql with something like this:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql
service mysql stop
service mysql start --init-file=/var/lib/mysql/init_file.sql
rm -f /var/lib/mysql/init_file.sql

or perhaps adding this to my.cnf

[mysqld]
init-file=/var/lib/mysql/init_file.sql

then restart mysql.

Give it a Try !!!

MySQL LOAD DATA – Convert ‘False’ String to Boolean

Convert the first value using user variables to load the True/False values.

Then, compare it to the value 'True'

PROPOSED SOLUTION

LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');

SAMPLE DATA

C:\bin>dir
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\bin

10/21/2014  10:01 AM    <DIR>          .
10/21/2014  10:01 AM    <DIR>          ..
10/21/2014  10:01 AM                23 input.txt
               1 File(s)             23 bytes
               2 Dir(s)  685,548,244,992 bytes free

C:\bin>type input.txt
"False","1"
"True","2"
C:\bin>

CODE FOR PROPOSED SOLUTION

USE test
DROP TABLE IF EXISTS n1;
CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');
SELECT * FROM n1;

CODE FOR PROPOSED SOLUTION EXECUTED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS n1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `n1` (
    ->   `c1` boolean ,
    ->   `c2` int
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\r\n' (@var1,c2)
    -> SET c1 = (@var1 = 'True');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM n1;
+------+------+
| c1   | c2   |
+------+------+
|    0 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql>