Title: Update optimization
Last modified: August 30, 2016

---

# Update optimization

 *  [jfortier](https://wordpress.org/support/users/jfortier/)
 * (@jfortier)
 * [10 years, 7 months ago](https://wordpress.org/support/topic/update-optimization/)
 * Hello plugin developers,
 * First off, I’d like to say thank you for this plugin! However, I’ve run into 
   some performance issues on some of my websites that have very large number of
   posts. So the least I can do is help you out.
 * I’ve got a very nice optimization for you for your update() method, specifically
   where you update the menu_order. I noticed that you are having php calculate 
   the menu_order using it’s result key in a foreach loop. This means that you are
   running an update for each and every row. I’ve got about 15,000 posts in one 
   of my dbs, and this has resulted in some very slow SQL. Slow enough that my PHP
   keeps timing out after 10s, and causing errors in my production servers. Anyhow,
   you can have SQL calculate the field’s row, and have it update itself resulting
   in a significant speed increase!
 * Here is the method I’ve altered:
 *     ```
       function refresh() {
               global $wpdb;
               $objects = $this->get_scporder_options_objects();
               $tags = $this->get_scporder_options_tags();
   
               if (!empty($objects)) {
                   foreach ($objects as $object) {
                       $result = $wpdb->get_results("
       					SELECT count(*) as cnt, max(menu_order) as max, min(menu_order) as min
       					FROM $wpdb->posts
       					WHERE post_type = '" . $object . "' AND post_status IN ('publish', 'pending', 'draft', 'private', 'future')
       				");
                       if ($result[0]->cnt == 0 || $result[0]->cnt == $result[0]->max)
                         continue;
   
                // Here's the optimization
                 $wpdb->query("SET @row_number = 0;");
                 $wpdb->query("
                   UPDATE $wpdb->posts as pt
                   JOIN (
                     SELECT ID, (@row_number:=@row_number + 1) AS rank
                     FROM $wpdb->posts
                     WHERE post_type = '$object' AND post_status IN ('publish', 'pending', 'draft', 'private', 'future')
                     ORDER BY menu_order ASC
                   ) as pt2
                   ON pt.id = pt2.id
                   SET pt.menu_order = pt2.rank;
                 ");
                 }
               }
   
               if (!empty($tags)) {
                   foreach ($tags as $taxonomy) {
                       $result = $wpdb->get_results("
       					SELECT count(*) as cnt, max(term_order) as max, min(term_order) as min
       					FROM $wpdb->terms AS terms
       					INNER JOIN $wpdb->term_taxonomy AS term_taxonomy ON ( terms.term_id = term_taxonomy.term_id )
       					WHERE term_taxonomy.taxonomy = '" . $taxonomy . "'
       				");
                       if ($result[0]->cnt == 0 || $result[0]->cnt == $result[0]->max)
                           continue;
   
                       $results = $wpdb->get_results("
       					SELECT terms.term_id
       					FROM $wpdb->terms AS terms
       					INNER JOIN $wpdb->term_taxonomy AS term_taxonomy ON ( terms.term_id = term_taxonomy.term_id )
       					WHERE term_taxonomy.taxonomy = '" . $taxonomy . "'
       					ORDER BY term_order ASC
       				");
                       foreach ($results as $key => $result) {
                           $wpdb->update($wpdb->terms, array('term_order' => $key + 1), array('term_id' => $result->term_id));
                       }
                   }
               }
           }
       ```
   
 * Here’s the raw SQL executed on 15,000 row so you can see how performant it is:
 *     ```
       mysql> SET @row_number = 0;
       Query OK, 0 rows affected (0.00 sec)
   
       mysql> UPDATE wp_posts as pt
           -> JOIN (
           -> SELECT ID, (@row_number:=@row_number + 1) AS rank
           -> FROM wp_posts
           -> WHERE post_type = 'post' AND post_status IN ('publish', 'pending', 'draft', 'private', 'future')
           -> ORDER BY menu_order ASC) as pt2
           -> ON pt.id = pt2.id
           -> SET pt.menu_order = pt2.rank;
       Query OK, 0 rows affected (0.18 sec)
       Rows matched: 15126  Changed: 0  Warnings: 0
       ```
   
 * As you can see it’s only taking 180ms to do what used to take over 10 seconds!
 * [https://wordpress.org/plugins/simple-custom-post-order/](https://wordpress.org/plugins/simple-custom-post-order/)

Viewing 1 replies (of 1 total)

 *  [Shmoo](https://wordpress.org/support/users/macpresss/)
 * (@macpresss)
 * [10 years, 7 months ago](https://wordpress.org/support/topic/update-optimization/#post-6679903)
 * People like you should get a statue on WordPress.org! 🙂
 * This is so important, I’ve updated this part of the code myself for now.
 * Thanks for posting this.

Viewing 1 replies (of 1 total)

The topic ‘Update optimization’ is closed to new replies.

 * ![](https://ps.w.org/simple-custom-post-order/assets/icon-256x256.jpg?rev=2969435)
 * [Simple Custom Post Order](https://wordpress.org/plugins/simple-custom-post-order/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/simple-custom-post-order/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/simple-custom-post-order/)
 * [Active Topics](https://wordpress.org/support/plugin/simple-custom-post-order/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/simple-custom-post-order/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/simple-custom-post-order/reviews/)

## Tags

 * [timeout](https://wordpress.org/support/topic-tag/timeout/)

 * 1 reply
 * 2 participants
 * Last reply from: [Shmoo](https://wordpress.org/support/users/macpresss/)
 * Last activity: [10 years, 7 months ago](https://wordpress.org/support/topic/update-optimization/#post-6679903)
 * Status: not resolved