1

I'm new to SQL, and I see this question has been asked many times, but none of the answers have helped me. So I'm hoping to get feedback on my situation.

I am trying to load a tab-delimited file with about 1.5 million rows and 48 columns. Each field is enclosed with double quotes. My query ran for over an hour and I killed it. Here is what I did:

CREATE TABLE mytable (
*48 variable declarations given types varchar, int, or decimal*,
PRIMARY KEY (id)
);

 

load data local infile 'MyFile.tsv'
into table mytable
fields terminated by '\t'
enclosed by '"'
lines terminated by '\n' IGNORE 1 LINES
(*comma separated list of all 48 variable names in 'mytable'*);

Is there something obviously that I'm missing that is blowing up the runtime of this query? I was careful to declare variables with only enough space as needed. So if I have an integer field with up to 3 digits, it is declared as myfield int(3).

Ibu
  • 42,752
  • 13
  • 76
  • 103
Ajreyn
  • 11
  • 4
  • How large is the file? This doesn't necessarily strike me as unusual, for such a large data set. – Tim Biegeleisen Jul 15 '16 at 04:51
  • Remember that you are loading the data once. As long as it loads the data. How long is slow? – Ibu Jul 15 '16 at 04:53
  • The speed of inserts is limited if you have too small configurations for the buffer pool, or the innodb log file. If you use the default values, they are probably too small. And ironically, if you make the buffer pool too *large* it can exceed physical memory can cause the server to swap, which also kills performance. – Bill Karwin Jul 15 '16 at 04:59
  • By the way, `INT(3)` does not mean what you think it means. See http://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20/3135854#3135854 – Bill Karwin Jul 15 '16 at 05:00

1 Answers1

-1

Increase the runtime by doing the following below:

Enable in Config File

If you want these changes to survive a reboot or restart of the mysql server, you’ll need to add them into your /etc/mysql/my.cnf configuration file for MySQL. Note that it might be in a different location on your installation.

Open up the file using a text editor in sudo or root mode, and then add these values if they don’t already exist in the file. If they do exist, just uncomment them.

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

Query caching can significantly improve the speed of your web application, especially if your application does mostly reads. Monitor the status using the methods above and see how it works over time.

@ref: http://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

unixmiah
  • 3,081
  • 1
  • 12
  • 26