PHP MySQL INSERT 1-3,000 rows as quickly and efficently as possible

Question

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

Answer 1

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.

Answer 2

Two ways of improve insertion speeds:

1. 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.

2. If the table backend is MyISAM (NOT InnoDB), do the INSERTs 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.