CODEWITHSUNDEEP
mysqlsqldatabase
Mellon
Mellon

Reputation: 38902

speed up data insertion

I am using MySQL database.

If I have 6,000,000 new records need to be inserted into a table (not a empty table).

Question 1:

Is

ALTER TABLE tbl_name DISABLE KEYS;

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9), ... ...

ALTER TABLE tbl_name ENABLE KEYS;
OPTIMIZE TABLE tbl_name;

faster than:

ALTER TABLE tbl_name DISABLE KEYS;

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
...
...

ALTER TABLE tbl_name ENABLE KEYS;
OPTIMIZE TABLE tbl_name;

?

Question 2:

Is the first one called bulk insertion?

-----------Update---------------

Should I enable/disable keys and optimize my table after? As @ Neil 's comment seems do not recommend to do so. What is others' opinion?

Upvotes: 2

Views: 575

Answers (3)

Gfox
Gfox

Reputation: 307

Please refer to this article.

By sending one query you will save several steps and win time. Specially when there is a question of insertion of hundred thousands of rows this time difference will be significant.

Upvotes: 1

Devart
Devart

Reputation: 122032

Try to optimize your queries using bulk inserts. This should considerably increase the speed of the inserting data process.

You wrote that you had encountered an error on inserting large amount of data - 'database gone away'.

In this case the size of the query should not exceed the maximal size of the packet - see the information about the max_allowed_packet variable.

How to check max_allowed_packet value - 

SELECT @@global.max_allowed_packet;

How to set this value -

SET @@global.max_allowed_packet = 200000;

More information here

If all this not enough for you, then have a look at this article (as Gfox suggested) - Speed of INSERT Statements.

Upvotes: 2

Mona Abdelmajeed
Mona Abdelmajeed

Reputation: 686

i Think Sending one query is more faster but With 6,000,000 record ,

Database will have problems ,

as i tried on windows ` insert 1000000 record as one query ,

i got this error database gone away :(

Upvotes: 1

Related Questions