How Can I Optimize Bulk Data Insertion into MySQLi for Speed and Security?

Enhanced Technique for Bulk Value Insertion in MySQLi

When confronted with the task of inserting a significant number of values into a MySQL database securely, developers often resort to repetitive prepared statements, which can be time-consuming for large datasets. This article explores a technique that enhances performance without compromising SQL injection security.

The Issue: Stack Overflows and Slow Insertion

A common approach is to utilize prepared statements inside a loop to insert each value individually. However, this method can lead to stack overflows or slow execution times when the dataset is large.

$array = ["array", "with", "about", "2000", "values"];

foreach ($array as $one) {
    $query = "INSERT INTO table (link) VALUES (?)";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("s", $one);
    $stmt->execute();
    $stmt->close();
}

The Solution: Transactions and Optimized Execution

To optimize the above code, we can employ a combination of transactions and improved execution strategy:

$array = ["array", "with", "about", "2000", "values"];
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

Implementation Details:

• Transactions: We initiate a transaction before inserting the values and commit it after all values are inserted. This ensures that if any error occurs during insertion, the changes will be rolled back.
• Optimized Execution: We move the prepared statement and binding operations outside the loop. This reduces the overhead of preparing the statement each time a value is inserted.

Performance Evaluation

Tests with 10,000 iterations demonstrate a significant performance improvement when using transactions:

• Without transaction: ~226 seconds
• With transaction: ~2 seconds

This optimization technique provides a two-order-of-magnitude speed increase, making it an efficient and secure solution for bulk value insertion in MySQL databases using MySQLi.

The above is the detailed content of How Can I Optimize Bulk Data Insertion into MySQLi for Speed and Security?. For more information, please follow other related articles on the PHP Chinese website!