How to insert a large number of posts/metas into WordPress quickly?

2 Views

I’m working on a plugin inserting into the database over 20.000 custom posts and each custom post has about 9 meta post entries.

This is taking MySQL a long time.Is there a better way to do this?

Read More

I insert the meta post data like this : 

$post_meta = array(
    '_stock_status'=>'instock',
    '_price'=>$r["price"],
    '_regular_price'=>$r["price_old"],
    '_sale_price'=>$r["price"],
    '_visibility'=>'visible',
    '_button_text'=>'buy',
    '_product_url'=>$r["link"],
    '_product_external_image_url'=>$r["imagelink"],  
    '_sku'=>$r["prdcrd"]
);

go_update_post_meta($post_id,$post_meta);

function go_update_post_meta($post_id,$meta){
   foreach($meta as $key=>$val){
      update_post_meta($post_id,$key,$val);
   }
}
Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Im doing a import plugin, and have kind of the same issue. 70-80 posts, and maybe 20 meta values each post.
    Im thinking you can store all your data in an array and do something like

    INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES ($post_id,$key1,$value1),($post_id,$key2,$value2) ,($post_id,$key3,$value3);

    Should be faster than the loop you are using now.

    Seeing your function, maybe this would work

    function go_update_post_meta($post_id,$meta){
   global $wpdb;

   $values = '';
   $a = 0;
   foreach($meta as $key=>$val){
      $a++;
      if ($a > 1) $values .=',';
      $values .= '('.$post_id.',"'.$key.'","'.$val.'")';
   }

   $sql = "INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES ".$values;
   $wpdb->query($sql);
}

    Tested and works in my code. Looks waaaay faster

  2. I think that there’s no better way of doing this. It’s really an inherent disadvantage of the way WordPress stores post meta data as lots of rows of key value pairs.

    Unfortunately, this leads to lots of updates and inserts in this scenario, which is why it’s slow.

    It would be worth checking if there is an index on the post_id and key columns, though. This might help speed up the update queries.