CODEWITHSUNDEEP
phpmysql
Amit Shah
Amit Shah

Reputation: 35

Speeding the process of MySql insert

I have a DB table which has approximately 40 columns and the main motive is to insert the records in the database as quickly as possible. I am using PHP for this.

The problems is, to create the insert statement, I have to loop through a for each. I am not sure if I doning this correctly. Please suggest me the best atlernative.. here is the example..

/// to loop through the available data ///

$sqc = "";
for ($i=1; $i<=100; $i++){
  if ($sqc == ""){
     $sqc = "('".$array_value["col1"]."'.. till .. '".$array_value["col40"]."',)";
  } else {
     $sqc .= ",('".$array_value["col1"]."'.. till .. '".$array_value["col40"]."',)";
  }
}

/// finally the sql query ///

$sql_quyery = "INSERT INTO  table_name (`col1`,.. till.. ,`col40`) values ".$sqc;

This concatenation of $sqc is taking a lot of time. and also the insertion in the DB, is there an alternate way of doing this.. i need to find a way to speed this up like 100X.. :(

Thank you

Upvotes: 1

Views: 115

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

Is just a small contribute but you can avoid the concat using a binding 

$stmt = mysqli_prepare($yuor_conn, 
        "INSERT INTO  table_name (`col1`,.. till.. ,`col40`) VALUES (?, ... till..  ?)");

mysqli_stmt_bind_param($stmt, 'ss.......s',
       $array_value["col1"], $array_value["col2"], .. till.., 
                  $array_value["col40"]);

Upvotes: 0

Ravi Karn
Ravi Karn

Reputation: 23

As suggested on MySQL Optimizing INSERT Statements page, there are some ways for this-

  • If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

  • When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

Find the link below- [MySQL Guide] [1] https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html

Upvotes: 1

Related Questions