Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 38 down vote favorite
36

I'm passing a large dataset into a mysql table via php using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of an mile long string and then executing it. I am using the codeigniter framework so its functions are also available to me.

share|improve this question
I have given answer according to your question for Codeigniter's multiple row insert. – Somnath Muluk Dec 19 '12 at 5:24
@SomnathMuluk Thank you, however it's been a while since I've needed to answer this question :)... – toofarsideways Dec 19 '12 at 17:39

6 Answers

up vote 82 down vote accepted

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row.

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

share|improve this answer
3  
Thanks for that! Btw your missing a closing bracket on the end of the function if anyone is planning on copying it. mysql_real_query('INSERT INTO table VALUES (text, category) '.implode(','. $sql)); – toofarsideways Apr 23 '09 at 2:21
1  
Thanks! Fixed. (I often do that...) – staticsan Apr 23 '09 at 2:30
1  
and the query should really be 'INSERT INTO table (text, category) VALUES '.implode(','. $sql)' sigh 4am coding leads to terrible debugging :( – toofarsideways Apr 23 '09 at 3:44
1  
the use of implode rocks. – cherouvim Jun 2 '10 at 6:00
1  
@staticsan: Thanks a lot! Superb Technique! Had a really significant speedup! – Legend Sep 13 '10 at 4:32
show 4 more comments
up vote 17 down vote

Multiple insert/ batch insert is now supported by codeigniter. I had same problem. Though it is very late for answering question, it will help somebody. That's why answering this question.

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name' ,
      'date' => 'My date'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name' ,
      'date' => 'Another date'
   )
);

$this->db->insert_batch('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
share|improve this answer
up vote 7 down vote

You could prepare the query for inserting one row using the mysqli_stmt class, and then iterate over the array of data. Something like:

$stmt =  $db->stmt_init();
$stmt->prepare("INSERT INTO mytbl (fld1, fld2, fld3, fld4) VALUES(?, ?, ?, ?)");
foreach($myarray as $row)
{
    $stmt->bind_param('idsb', $row['fld1'], $row['fld2'], $row['fld3'], $row['fld4']);
    $stmt->execute();
}
$stmt->close();

Where 'idsb' are the types of the data you're binding (int, double, string, blob).

share|improve this answer
2  
I recently ran some benchmarks comparing bulk insert and prepared insert statements as mentioned here. For around 500 inserts, the prepared inserts method completed in between 2.6-4.4 secs, and the bulk insert method in 0.12-0.35 secs. I would have thought mysql would have "bulked" these prepared statements together and perform just as well as the bulk inserts, but in a default set-up, the performance difference is huge apparently. (Btw all benchmarked queries were running inside a single transaction for each test, as to prevent auto-committing) – Motin Jul 27 '11 at 16:53
up vote 3 down vote

Well, you don't want to execute 1000 query calls, but doing this is fine:

$stmt= array( 'array of statements' );
$query= 'INSERT INTO yourtable (col1,col2,col3) VALUES ';
foreach( $stmt AS $k => $v ) {
  $query.= '(' .$v. ')'; // NOTE: you'll have to change to suit
  if ( $k !== sizeof($stmt)-1 ) $query.= ', ';
}
$r= mysql_query($query);

Depending on your data source, populating the array might be as easy as opening a file and dumping the contents into an array via file().

share|improve this answer
It's cleaner if you move that if above the query and change it to something like if ($k>0). – cherouvim Jun 2 '10 at 6:01
@cherouvim... umm, thanks? This post is from over a year ago... – bdl Jun 2 '10 at 14:13
Yep. The information stays forever though :) – cherouvim Jun 2 '10 at 15:05
@cherouvim... Well, you're right about that. Thanks for your input. As I'm re-reading the example I provided,I'm failing to see your point. Care to elaborate (via pastebin, etc?). Thanks- – bdl Jun 3 '10 at 14:19
1  
pastebin.com/Rv7EaZYH – cherouvim Jun 3 '10 at 18:04
up vote 3 down vote

You could always use mysql's LOAD DATA:

LOAD DATA LOCAL INFILE '/full/path/to/file/foo.csv' INTO TABLE `footable` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'

to do bulk inserts rather than using a bunch of INSERT statements.

share|improve this answer
I had looked into that but I need to manipulate the data before inserting it. It's been given to me as a Cartesian product of a 1400 by 1400 set of int values many of which are zero. I need to convert that to a many to many relationship using an intermediary table to save space hence the need for inserts as opposed to a bulk insert – toofarsideways Apr 23 '09 at 2:26
You can always generate a csv file after manipulating it and calling the mysql statement that loads the data – Alexander Jardim Jun 28 at 18:55
up vote 3 down vote

I know this is an old query, but I was just reading and thought I'd add what I found elsewhere:

mysqli in PHP 5 is an ojbect with some good functions that will allow you to speed up the insertion time for the answer above:

$mysqli->autocommit(FALSE);
$mysqli->multi_query($sqlCombined);
$mysqli->autocommit(TRUE);

Turning off autocommit when inserting many rows greatly speeds up insertion, so turn it off, then execute as mentioned above, or just make a string (sqlCombined) which is many insert statements separated by semi-colons and multi-query will handle them fine.

Hope this helps someone save time (searching and inserting!)

R

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.