NickName:Peter Guan Ask DateTime:2013-11-04T15:19:22

Optimizing MySQL or table design

I'm puzzled with my SQL homework. I'm not an expert in databases. I'm using MySQL.

I have a table named 'news'

create table news
    title text not null,
    time DATE,
    ip varchar(15),
    reporter varchar(25),
    tag varchar(20),
    id int not null auto_increment,
    primary key(id)
)engine=innodb;

And I have a txt file 'news2.txt' with 1000000 lines of data.

I want to optimize my sql for speed.

Here is my SQL statement

SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;

load data local infile "e:/news2.txt" into table news fields terminated by ',' LINES TERMINATED BY '\n' (title,time,ip,reporter,tag); 

SET UNIQUE_CHECKS=1;
SET AUTOCOMMIT=1;

the 'news2.txt' contains like*'Vivamus dictum ultrices Curae aliquet vel nunc Aenean,2013/10/8,10.0.0.252,Reed Wyatt,Science'* it's a row.

The execution time is 29.188s. Are there any problems with my table design? What can I do to make it faster?

Copyright Notice：Content Author:「Peter Guan」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/19763232/optimizing-mysql-or-table-design

Answers

Mihai Stancu 2013-11-04T09:15:20

Data load\n\nOne million entries take some time to write to disk. Check the size of the table and imagine that it's not just copying a file it's doing some computations in order to save it as a DB record.\n\nIt's not something to be concerned about unless the homework clearly stats that speed is of the essence (which I doubt it would).\n\nIn a normal application inserting one million entries into the news table in 26s is exceedingly good performance... not to mention \"where did the find all of those news articles\"? In real life the application layer will most likely slow things down.\n\nTransactions\n\nYou could remove the \"autocommit=0\" from there (if you don't need it), because that means the entire load operation is being run transactionally which means the DB engine needs to write everything in a temporary place and check if every insert (of the one million you have) was successful before committing the data.\n\nRunning with \"autocommit=1\" would let the DB engine handle each insert as a transaction. \n\nUsually one transaction per statement creates some degree of \"transactional\" overhead, and it's a good practice to run batches of transactions (50-100 statements per transaction) to minimize that overhead, but in the case of very large transactions the overhead builds up and cannot be held in memory so it needs to be written to disk which makes things work slower -- in this case it's better to use one transaction per statement (which can be run from memory) rather than one transaction per entire batch.",