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://www.ads-software.com/plugins/simple-custom-post-order/