Best way to INSERT many values in mysqli?

by

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your loop.

$array = 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");

I tested this code with 10,000 iterations on my web server.

Without transaction: 226 seconds.
With transaction: 2 seconds.
Or a two order of magnitude speed increase, at least for that test.

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. Commands out of sync; you can’t run this command now
  3. Fatal error: Call to a member function bind_param() on boolean [duplicate]
  4. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  5. When should I use MySQLi instead of MySQL?
  6. How to get count of rows in MySQL table using PHP?
  7. Example of how to use bind_result vs get_result
  8. mysqli::query(): Couldn’t fetch mysqli
  9. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  10. PHP MySQLI Prevent SQL Injection [duplicate]
  11. What is the difference between MySQL, MySQLi and PDO? [closed]
  12. How to test if a MySQL query was successful in modifying database table data?
  13. How can I enable the MySQLi extension in PHP 7?
  14. mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in [duplicate]
  15. How can I use mysqli_fetch_array() twice?
  16. PHP Commands Out of Sync error
  17. Mysql No connection could be made because the target machine actively refused it
  18. MYSQLi error: User already has more than ‘max_user_connections’ active connections [duplicate]
  19. using nulls in a mysqli prepared statement
  20. How to connect to MySQL database in PHP using mysqli extension?
  21. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  22. Fatal error: Call to undefined function mysqli_result()
  23. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  24. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  25. Strange problem with the length of the field gotten from database [duplicate]
  26. PHP: mysql v mysqli v pdo [closed]
  27. How do I ensure I caught all errors from MySQLi::multi_query?
  28. Cannot figure out how to run a mysqli_multi_query and use the results from the last query
  29. How to get a list of databases?
  30. Why cannot the PHP hide the error message thrown by the MySQL connection?

Leave a Comment