[Plugin: Media Library Categories] Poor scalability

I imported the entire icon collection from http://mapicons.nicolasmollet.com/ into my media library and now it contains 60+ categories and 800+ attachments. As a result, an attempt to display the Media library page of the Add image dialog times out at 30 seconds.

The problem is that whenever you build a tree of categories, you issue a separate query for each category to obtain its children. This doesn’t scale. You should get the whole taxonomy with one query and build the tree in code.

Even worse, get_category_hierarchical_terms() gets all *attachments* for a category, and for each one of them calls wp_get_attachment_thumb_url(), which calls get_post()! So in the end get_category_hierarchical_terms() performs N_categories + N_attachments queries to the database. And here’s the kicker: the whole process is repeated 10x in order to display the Media library page, because for every item on the results page the category tree must be built for the select widget. That’s ~10K queries in my case.

As a quick fix, I added some result caching (with wp_cache) to some methods, and rewrote get_media_categories() so that it only runs one query, builds the hierarchy in advance, and stores precomputed results in the cache:


function get_media_categories($args = array())
{
global $wpdb;
$ret = array();
if(empty($args))
{
$args = array(
'parent' => 0,
'taxonomy' => mc::$taxonomy,
'orderby' => 'term_order',
'order' => 'ASC'
);
}
ksort($args); $queryKey=md5(serialize($args));
$cret=wp_cache_get($queryKey,'mc::gmc');
if ($cret===false && isset($args['parent'])) {

//use args without the parent parameter to get all categories
$args2 = array_diff_key($args,array('parent'=>'')); ksort($args2);
$cats = $this->get_media_categories($args2);

$childrenByParentID = array();

//create arrays to hold children of each category
foreach($cats as &$cat) $childrenByParentID[$cat->term_id]=array();

//account for root categories too
$childrenByParentID[0]=array();

//build child collections
foreach($cats as &$cat) $childrenByParentID[$cat->parent][]=&$cat;

//store precomputed results in wp_cache
foreach($childrenByParentID as $id => &$children) {

//synthesize the signature
$args2['parent'] = $id; ksort($args2);
$childrenKey=md5(serialize($args2));

//store the result
wp_cache_set($childrenKey,$children,'mc::gmc');
}
}
$cret=wp_cache_get($queryKey,'mc::gmc');
if ($cret!==false) return $cret;
$query ="
SELECT
tax.term_id,
term.name,
term.slug,
term.term_group,
tax.term_taxonomy_id,
tax.taxonomy,
tax.description,
tax.parent,
tax.count,
term.term_order,
tax.count as category_count,
tax.description as category_description,
term.name as cat_name,
term.slug as category_nicename,
tax.parent as category_parent
FROM $wpdb->terms term
INNER JOIN $wpdb->term_taxonomy tax ON tax.term_id = term.term_id
WHERE ";

if(isset($args['parent']))
{
$query .= "parent = ".$args['parent']." AND ";
}
$query .= "taxonomy = '".$args['taxonomy']."'
order by ".$args['orderby']." ".$args['order']."
";
if(array_key_exists("number",$args) && array_key_exists("offset",$args))
{
$query.= " LIMIT ".$args['offset'].", ".$args['number'];
}

$categories = $wpdb->get_results( $query );

if (empty($categories)) {

wp_cache_set($queryKey,$ret,'mc::gmc');
return $ret;
}

wp_cache_set($queryKey,$categories,'mc::gmc');
return $categories;

}

http://ww.wp.xz.cn/extend/plugins/media-library-categories/