wp_insert_post extremely slow on big table, direct query very fast

by

My posts table is about 600K, and I don’t consider this big at all. Anyway wp_insert_post is becoming very slow. I am trying to import a huge list of hotel information into Posts. The list is about 1500K big. Inserting posts starts nicely with 200-400 posts per second but it dramatically drops down to abt 20-10 posts per second when it reaches 400K posts.

When I do a direct query on the posts table it is inserting 400 posts per second again. So my conclusion is that wp_insert_post is slowing things down considerably.

I tried some ‘tricks’ to improve wp_insert_post speed such as :

wp_defer_term_counting( false );
wp_defer_comment_counting( false );
$wpdb->query( 'SET autocommit = 0;' );

and after the bulk import:

wp_defer_term_counting( true );
wp_defer_comment_counting( true );
$wpdb->query( 'SET autocommit = 1;' );
$wpdb->query( 'COMMIT;' );

but that doesn’t help improving the speed of insert, actually the improvement is none.

Can someone please explain to me what ‘check’ in the wp_insert_post function is slowing it down?

Is it column ‘post_name’ AKA slug ? it must be unique, right? Are there any other columns that are checked on?

I would like to know what is checked, or what NEEDS to be checked because I am seriously thinking of overriding wp_insert_post function with my own to speed things up.

thanks in advance

edit – added my loop :

foreach($hotels as $key => $hotel) {

    $slug  = $hotel->HotelName;
    $title = $hotel->HotelName;


if ($hotel->translation == NULL) {
    $description = $hotel->Overview;
    } else {
    $description = $hotel->translation;
    }

    $hotel_id = wp_insert_post(
        array(
            //'ID'              =>  $hotel->id,
            'post_mime_type'    =>  $hotel->id,
            'comment_status'    =>  'closed',
            'ping_status'       =>  'closed',
            'post_author'       =>  $author_id,
            'post_name'         =>  $slug,
            'post_title'        =>  $title,
            'post_status'       =>  'publish',
            'post_type'         =>  'hotel',
            'post_content'      =>  $description, )
    );

    }

3 s
3

you should revers the code you should add this befor the import 

wp_defer_term_counting( true );
wp_defer_comment_counting( true );

don’t set it false and after the import you set it true you must do the oppsite thing
i had the same issue to insert 50 posts it took about 7 mintues after i added this code befor the wp_insert_post it took just 7 seconds
and you can add this too

define( 'WP_IMPORTING', true );

and after you imported the post add this and run the script

wp_defer_term_counting( false );
wp_defer_comment_counting( false );

Leave a Comment