Get the most popular terms for a custom post type

I if were you, I’d have two options in my mind. Both hinge on the fact thsi is an inherently expensive computation and no mysql trickery will reduce the load with the data you have.

So

Option 1, do it the expensive way and store the data for a rainy day

You’re going to have to swallow the cost of calculating this, but you don’t have to do it over and over again. Calculate it the long expensive way and save the post IDs in a transient with a long expiration time. You’ll have a one time hit followed by it running super fast until the transient runs out.

For bonus points, do the calculation in a wp-cron task once a day and set the expiration time to two days,

Option 2, create helper data

You have 1 taxonomy and 6 post types occupying it. To get around this, create another taxonomy, a hidden taxonomy with no GUI. This taxonomy will only be attached to your desired post type.

Then, hook into the term creation/deletion/assignment hooks, and use them to duplicate the taxonomy data but only for that post type. That way when you want to do your list, you query the second hidden taxonomy, which acts as a cache of sorts. All the hard work of figuring out which term has how many is done as you make the changes

Just because you don’t know how to handle the data you have performantly, doesn’t mean there is a solution, but changing the data made available to you can sometimes make a world of difference, and that doesn’t always mean adding more cache.

Here’s a crude example on cloning a taxonomy:

class Clone_Taxonomy {
    public $from = '';
    public $to = '';
    public function __construct($from, $to){
        $this->from = $from;
        $this->to = $to;
        add_action( 'set_object_terms', array(&$this,'sync_set_object_terms'),100,6);
    }

    public function sync_set_object_terms($object_id, $terms, $tt_ids, $taxonomy, $append, $old_tt_ids){
        if($taxonomy == $this->from){
            // duplicate to ovum_research_authors taxonomy
            //$terms2 = array();
            $r = wp_set_object_terms( $object_id, $terms, $this->to, $append );
            if(!is_array($r)){
                // things failed for whatever reason
            }
        }

    }
}

$clone_tax = new Clone_Taxonomy('taxonomy1','taxonomy2');

Place a second check after checking the taxonomy to figure out which post type it is and if the post type matches, clone the term, else don’t.