6

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, )
    );

    } 
  • How are you looping through and running your queries? Knowing that might help to point out any issues as more often than not what you are seeing is due to not freeing up resources properly. – Privateer Jan 31 '15 at 6:02
  • So you're importing 1.5 Million posts? I think you should do the wp_defer_term_counting vice versa, i.e. first true before the run and then false after it - I think defer means to postpone something. – birgire Jan 31 '15 at 11:39
  • yeah you are correct. But anyway wp_defer_term_counting doesn't apply to my loop because I am not using any terms. Those will come when I maximized the speed insertion of just simple posts. The function wp_insert_post is extremely slow, and I wonder why. – user1973842 Jan 31 '15 at 13:28
  • Are you doing it on vanila wordpress, with no plugins active? – Mark Kaplun Jan 31 '15 at 14:52
  • 1
    Hi Birgire, the issue is not that inserting the data into table posts is slow. The issue is that checking for unique SLUG is extremely slow. Before every insert WP checks if the SLUG provided is unique, and if not it will add -01 or -02 etc etc. – user1973842 Feb 3 '15 at 12:20
2

Try modifying the database to improve the lookup speed of the post_name field...

ALTER TABLE `wp_posts` ADD INDEX (`post_name`);

You want to avoid writing your own wp_insert_post() replacement as there are little tricks that occur in unexpected places that become a real headache to debug later on.

  • I thought that Indexes make reading data faster, but will make writing to the database slower because the index has to be rebuilt. – Mr. Me Mar 11 '18 at 2:55
  • 1
    @Mr.Me In this case the issue was not with how fast the inserts occur, but with how fast the wp_insert_post() function performs. Since the wp_insert_post() function is ~500 lines of validation to ensure that fields are properly filled the slowdown occurs due to those validation routines. In particular the search through the existing list of slugs (the "post_name" field) gets much slower as the number of post entries increases. By creating an index on that field you increase the rate of those look-ups and thus make the execution faster overall. – BA_Webimax Mar 12 '18 at 16:39
2

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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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