Geek asks and answers

Paste into database

I have to write a lot of data to the MySQL database about 5 times per second. The fastest way: insert every 1/5 second or make a queue and insert all the saved data every ~ 5 seconds? If the second method is better - is it possible to insert into 1 table using 1 query of several rows?

+5
source share
5 answers

Given the frequency of insertions, it is better to go with a second approach, which is in line and than add at a time.!

But first you should consider these scenarios:

  • ? , , ( ~ 5 )?

  • / , , , .

+2

innodb_buffer_pool_instances. . Partitioning .
XML.

+2

, , . , , , .

MySQL :

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

You can do an insert with all the data with something like this:

INSERT INTO table (field1, field2,... , fieldN )
VALUES 
(value1_1', value1_2,... , value1_N),
(value2_1', value2_2,... , value2_N),
...
(valueM_1', valueM_2,... , valueM_N);
+1
source

My experience is that it’s faster to work with packages when inserting data into a MySQL database.

Thus, the parameter executes several insertion requests more slowly :

    INSERT INTO my_table VALUES (1, "a");
    INSERT INTO my_table VALUES (2, "b");

The parameter is faster :

    INSERT INTO my_table VALUES (1, "a"), (2, "b");
+1
source

More articles:


All Articles