CODEWITHSUNDEEP
mysqlinsert
Max Frai
Max Frai

Reputation: 64286

Insertion into database

I have to write into MySQL database a lot of data for about 5 times per second. What is the fastest way: insert each 1/5 of second or make a queue and insert all stored data each ~5 seconds? If the second way is better - is it possible to insert into 1 table using 1 request a few rows?

Upvotes: 5

Views: 169

Answers (5)

Demerzel
Demerzel

Reputation: 11

You can make an insert with all the data witho 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);

Upvotes: 1

Anuj Patel
Anuj Patel

Reputation: 17859

Considering the frequency of the insertions Its better to go with the second approach that is queuing and than adding at one go.!

But You should consider these scenarios first :

  1. Is your system Real Time.? Yes then what is the maximum delay that you can afford (As it'll take ~5 seconds for next insertion and data to be persisted/available)?

  2. What are the chances of Incorrect values/Errors to come in data, as if one data is incorrect you'll loose rest all if the query has to fail.

Upvotes: 2

akalucas
akalucas

Reputation: 476

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

So the slower option is executing multiple insert queries:


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

The faster option would be:


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

Upvotes: 1

deadly
deadly

Reputation: 1192

As each transaction comes with a fixed cost, I'd say that doing a multi-line insert every few seconds is better. With some of the systems we use at work we cache hundreds of lines before inserting them all in one go.

From the MySQL documentation you can do a multi-line insert like so:

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

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Using multiple buffer pools with innodb_buffer_pool_instances. it can depend on number of cores onmachine. Use Partitioning of table.
You can collectively insert data using XML.

Upvotes: 2

Related Questions