problem with function category_node_get_categories

I changed the function from the above query to this:

$result = db_query(db_rewrite_sql('SELECT n.nid, r.nid AS node_id, c.*, n.title FROM {category} c INNER JOIN {category_node} r ON c.cid = r.cid INNER JOIN {category} cn ON c.cnid = cn.cid INNER JOIN {node} n ON c.cid = n.nid INNER JOIN {node} cnn ON cn.cid = cnn.nid WHERE r.nid = %d AND n.status = 1 AND n.moderate = 0 ORDER BY cn.weight, cnn.title, c.weight, n.title'), $nid);

Note the new "WHERE r.nid=%d".

This reduced the query time from 2 seconds to 0.003 seconds. Thats much better! My concern is the impact on the module.

What will I break by doing this?

The full tweaked function:

/**
 * Find all categories associated to the given node, ordered by container and category weight.
 */
function category_node_get_categories($nid, $key = 'cid', $reset = FALSE) {
  static $categories;

  if (!isset($categories) || $reset) {
    $categories = array();

    $result = db_query(db_rewrite_sql('SELECT n.nid, r.nid AS node_id, c.*, n.title FROM {category} c INNER JOIN {category_node} r ON c.cid = r.cid INNER JOIN {category} cn ON c.cnid = cn.cid INNER JOIN {node} n ON c.cid = n.nid INNER JOIN {node} cnn ON cn.cid = cnn.nid WHERE r.nid = %d AND n.status = 1 AND n.moderate = 0 ORDER BY cn.weight, cnn.title, c.weight, n.title'), $nid);

    while ($category = db_fetch_object($result)) {
      $categories[$category->node_id][$category->$key] = $category;
    }
  }

  return isset($categories[$nid]) ? $categories[$nid] : array();
}

Can someone review this?

Is this one gonna go the same way as my category counting bug report which I realised wasn't a bug at all?

Why is it static? Surely you can only view one node at a time?!

I've just tried to do a full import of my data (about 350 categories + 12,000 articles... This function causes PHP to use 78MB RAM (I had to up the limit from 24 to 128 just to get it to work). The query takes between 12 and 15 seconds and then there is the PHP time on top of that which takes the entire page load time to over 20 seconds on a dual processor Xeon (2x1Ghz PIII) with 1.5Gb RAM.

Surely this isn't the best way to do it. Can someone confirm if my patch breaks anything I dont know about?

You know - it was so long ago now, I honestly cant remember. I gave up with the category module and went back to taxonomy. The category module is far to unstable for large public site usage (imho)...

I'm pretty sure those stats were without my patch (if memory serves...)

But, like I said, this module was not stable enough for use on our site (Sportbusiness.com) so I moved back to taxonomy which does EVERYTHING we want it to do (some things required a little persuasion...)