Add index on comment parent

The pid field of the comment table is not indexed. Yet we have this query in comment.module:

/**
 * Get replies count for a comment.
 *
 * @param $pid
 *   The comment id.
 * @return
 *   The replies count.
 */
function comment_num_replies($pid) {
  static $cache;

  if (!isset($cache[$pid])) {
    $cache[$pid] = db_result(db_query('SELECT COUNT(cid) FROM {comments} WHERE pid = %d AND status = %d', $pid, COMMENT_PUBLISHED));
  }

  return $cache[$pid];
}

The cache helps, but the query does a full table scan. On my site, adding the index reduces the query from looking at, e.g., 15,000+ rows to just a few rows.

Patch to comment.install adds the index and provides an update.