Share tips on optimizing insert into statements
MYSQL batch insert database to achieve statement performance analysis
Assume that our table structure is as follows
The code is as follows
CREATE TABLE example ( example_id INT NOT NULL, name VARCHAR( 50 ) NOT NULL, value VARCHAR( 50 ) NOT NULL, other_value VARCHAR( 50 ) NOT NULL )
Normally we will write a single inserted SQL statement like this :
The code is as follows
INSERT INTO example (example_id, name, value, other_value) VALUES (100, 'Name 1', 'Value 1', 'Other 1');
Mysql allows us to insert data in batches in a sql statement, as follows sql statement:
The code is as follows
INSERT INTO example (example_id, name, value, other_value) VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4');
If we insert If the order of the columns is consistent with the order of the columns in the table, you can also omit the definition of the column names. The following sql
code is as follows
INSERT INTO example VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4');
The above does not seem to be a problem, let me use the sql statement below The optimization tips will be tested separately below. The goal is to insert 2 million pieces of data into an empty data table
The first method: Use insert into, the code is as follows:
$params = array('value'=>'50');
set_time_limit(0);
echo date("H:i:s");
for($i=0;$i<2000000;$i++){
$connect_mysql->insert($params);
};
echo date("H:i:s");The final display is: 23:25:05 01:32:05, which means it took more than 2 hours!
Second method: Use transaction submission and batch insert The last displayed time consumed by the database (submitted every 10W items) is: 22:56:13 23:04:00, a total of 8 minutes and 13 seconds. The code is as follows:
echo date("H:i:s");
$connect_mysql->query('BEGIN');
$params = array('value'=>'50');
for($i=0;$i<2000000;$i++){
$connect_mysql->insert($params);
if($i%100000==0){
$connect_mysql->query('COMMIT');
$connect_mysql->query('BEGIN');
}
}
$connect_mysql->query('COMMIT');
echo date("H:i:s");The third method : Use optimized SQL statements: splice the SQL statements, use insert into table () values (),(),(),() and then insert them all at once. If the string is too long,
Then you need to configure MYSQL and run it in the mysql command line: set global max_allowed_packet = 2*1024*1024*10; The consumption time is: 11:24:06 11:25:06;
Insert 200W records It only took 1 minute to test the data! The code is as follows:
$sql= "insert into twenty_million (value) values";
for($i=0;$i<2000000;$i++){
$sql.="('50'),";
};
$sql = substr($sql,0,strlen($sql)-1);
$connect_mysql->query($sql);Finally, to summarize, the first method is undoubtedly the worst when inserting large batches of data, while the second method is worse in practical applications. Widely, the third method is more suitable when inserting test data or other low requirements, and it is really fast.
【Related Recommendations】
1. Share a tutorial for inserting multiple records in batches using an insert statement
2. Commonly used in parsing mysql Three insert statements and their differences
The above is the detailed content of Share tips on optimizing insert into statements. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to optimize settings and improve performance after receiving a new Win11 computer?
Mar 03, 2024 pm 09:01 PM
How do we set up and optimize performance after receiving a new computer? Users can directly open Privacy and Security, and then click General (Advertising ID, Local Content, Application Launch, Setting Recommendations, Productivity Tools or directly open Local Group Policy Just use the editor to operate it. Let me introduce to you in detail how to optimize settings and improve performance after receiving a new Win11 computer. How to optimize settings and improve performance after receiving a new Win11 computer. One: 1. Press the [Win+i] key combination to open Settings, then click [Privacy and Security] on the left, and click [General (Advertising ID, Local Content, App Launch, Setting Suggestions, Productivity) under Windows Permissions on the right Tools)】.Method 2
In-depth interpretation: Why is Laravel as slow as a snail?
Mar 07, 2024 am 09:54 AM
Laravel is a popular PHP development framework, but it is sometimes criticized for being as slow as a snail. What exactly causes Laravel's unsatisfactory speed? This article will provide an in-depth explanation of the reasons why Laravel is as slow as a snail from multiple aspects, and combine it with specific code examples to help readers gain a deeper understanding of this problem. 1. ORM query performance issues In Laravel, ORM (Object Relational Mapping) is a very powerful feature that allows
Decoding Laravel performance bottlenecks: Optimization techniques fully revealed!
Mar 06, 2024 pm 02:33 PM
Decoding Laravel performance bottlenecks: Optimization techniques fully revealed! Laravel, as a popular PHP framework, provides developers with rich functions and a convenient development experience. However, as the size of the project increases and the number of visits increases, we may face the challenge of performance bottlenecks. This article will delve into Laravel performance optimization techniques to help developers discover and solve potential performance problems. 1. Database query optimization using Eloquent delayed loading When using Eloquent to query the database, avoid
C++ program optimization: time complexity reduction techniques
Jun 01, 2024 am 11:19 AM
Time complexity measures the execution time of an algorithm relative to the size of the input. Tips for reducing the time complexity of C++ programs include: choosing appropriate containers (such as vector, list) to optimize data storage and management. Utilize efficient algorithms such as quick sort to reduce computation time. Eliminate multiple operations to reduce double counting. Use conditional branches to avoid unnecessary calculations. Optimize linear search by using faster algorithms such as binary search.
Discussion on Golang's gc optimization strategy
Mar 06, 2024 pm 02:39 PM
Golang's garbage collection (GC) has always been a hot topic among developers. As a fast programming language, Golang's built-in garbage collector can manage memory very well, but as the size of the program increases, some performance problems sometimes occur. This article will explore Golang’s GC optimization strategies and provide some specific code examples. Garbage collection in Golang Golang's garbage collector is based on concurrent mark-sweep (concurrentmark-s
Laravel performance bottleneck revealed: optimization solution revealed!
Mar 07, 2024 pm 01:30 PM
Laravel performance bottleneck revealed: optimization solution revealed! With the development of Internet technology, the performance optimization of websites and applications has become increasingly important. As a popular PHP framework, Laravel may face performance bottlenecks during the development process. This article will explore the performance problems that Laravel applications may encounter, and provide some optimization solutions and specific code examples so that developers can better solve these problems. 1. Database query optimization Database query is one of the common performance bottlenecks in Web applications. exist
How to optimize the startup items of WIN7 system
Mar 26, 2024 pm 06:20 PM
1. Press the key combination (win key + R) on the desktop to open the run window, then enter [regedit] and press Enter to confirm. 2. After opening the Registry Editor, we click to expand [HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionExplorer], and then see if there is a Serialize item in the directory. If not, we can right-click Explorer, create a new item, and name it Serialize. 3. Then click Serialize, then right-click the blank space in the right pane, create a new DWORD (32) bit value, and name it Star
What are some ways to resolve inefficiencies in PHP functions?
May 02, 2024 pm 01:48 PM
Five ways to optimize PHP function efficiency: avoid unnecessary copying of variables. Use references to avoid variable copying. Avoid repeated function calls. Inline simple functions. Optimizing loops using arrays.
