Menu
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?
259
Data insertion is the process of inserting rows into a table. The data insertion methods and an example of specifying SQL statements are shown as follows. Data insertion methods. The INSERT statement is used to insert rows.
Simple INSERT statement to add data to the table. Use INSERT Statement to add multiple rows in the table. INSERT INTO SELECT clause to insert the output generated by the SELECT query. INSERT IGNORE clause to ignore the error generated during the execution of the query.
If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.
The INSERT INTO statement is used to insert new records in a table.
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 :
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)?
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.
119
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.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
