I am looking for the fastest way to INSERT
1-3,000 rows into a MySQL database using PHP. My current solution is taking around 42 seconds to insert the rows which I think that could be much faster.
I am using a self-written DB class, the insert()
method takes two params (string) $table
and (array) $vars
. The $items
array is an associative array where the key is the column name in the table and the value is the value to insert. This works really well for because I sometimes have 30 columns in a table and already have the data there in an array. The insert()
method is below:
function insert($table,$vars) {
if(empty($this->sql_link)){
$this->connection();
}
$cols = array();
$vals = array();
foreach($vars as $key => $value) {
$cols[] = "`" . $key . "`";
$vals[] = "'" . $this->esc($value) . "'";
}
//join the columns and values to insert into sql
$fields = join(', ', $cols);
$values = join(', ', $vals);
$insert = mysql_query("INSERT INTO `$table` ($fields) VALUES ($values);", $this->sql_link);
return $insert;
}
It should be self-explanatory but basically I take the keys and values from $vars and create an INSERT statement. It works, I think the problem I am having is sending the queries one at a time.
Should I build a long query string?
INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);
and send it all at one time? If so can this handle 3,000 insert statements in one call?
Is there another way I am not looking at? Any info is appreciated.
Thanks
The most performant way is to use the multiple-row insert syntax:
INSERT INTO table (field, field2, etc) VALUES (1, 2, etc),(1, 2, etc),(1, 2, etc);
Manual:
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses.
Two ways of improve insertion speeds:
At the start, before any INSERT
, do a mysql_query("START TRANSACTION");
or a simpler mysql_query("BEGIN");
. At the end, do a mysql_query("COMMIT");
. These two lines, speeds up the bulk insertion a 5-10x performance.
If the table backend is MyISAM
(NOT InnoDB), do the INSERT
s followed with the word DELAYED
. For example, instead of INSERT INTO table
use INSERT DELAYED INTO table
for an aditional 10-15x speed-up.
If you combine the 2 methods, is posible to achieve a speed-up of 100 times.
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