Speed up insert query mysql

问题:

I want to insert about 10000 new rows to an existing table which contain 10000 rows already. I need to get the insert id for the query and to use that id in another function inside the loop as in the code below.

foreach($values as $val){
  $sql = "INSERT INTO wp_posts (`post_author`, `post_date`, `post_content`, `post_title`, `post_excerpt`, `post_status`,`post_modified`,`post_type`)
  VALUES('".$val[author]."',NOW(),'".$val[content]."','".$val[title]."','".$val[excerpt]."','published',NOW(),'post')";
  $result = mysql_query($sql);
  $insertid = mysql_insert_id();
  add_post_meta($insertid, $val[metakey], $val[metaval], true );
}

I am fetching the values for $values array from a CSV file. As of now, I can insert only 20 new rows per 3 minutes. Is there anyway to speed up this insertion?

回答1:

You could try using a prepared statement with PDO, see if it helps.

$sql =     "INSERT INTO wp_posts (`post_author`, `post_date`, `post_content`, `post_title`, `post_excerpt`, `post_status`,`post_modified`,`post_type`)
            VALUES(:author,NOW(),:content,:title,:excerpt,'published',NOW(),'post')";
$pdoStatement = $pdoConnexion->prepare($sql);

foreach($values as $val){
    $pdoStatement->bindValue(':author', $val['author'], PDO::PARAM_STR);
    $pdoStatement->bindValue(':content', $val['content'], PDO::PARAM_STR);
    $pdoStatement->bindValue(':title', $val['title'], PDO::PARAM_STR);
    $pdoStatement->bindValue(':excerpt', $val['excerpt'], PDO::PARAM_STR);
    $pdoStatement->execute();
    $insertid = $pdoConnexion->lastInsertId();
    add_post_meta($insertid, $val[metakey], $val[metaval], true );
}