Optimize queries.

Because I felt the badges having these optimizations are essential if you have a lot of badges, I'll paste the optimizations I felt were useful to me while creating the 6.x update. Someone who knows how to create indexes in pgsql would be of help here ^_^:

function user_badges_install() {
  $ret = array();
  $ret[] = db_query("CREATE TABLE {user_badges_badges} (
    bid int(10) NOT NULL default '0',
    name varchar(50) NOT NULL default '',
    image varchar(80) NOT NULL default '',
    weight int(2) NOT NULL default '0',
    PRIMARY KEY (bid),
    KEY bid_weight_name(bid, weight, name),
    KEY weight_name(weight, name)
  ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
  $ret[] = db_query("CREATE TABLE {user_badges_product} (
    bid int(10) NOT NULL default '0',
    nid int(10) NOT NULL default '0'
    PRIMARY KEY (bid, nid),
  ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
  $ret[] = db_query("CREATE TABLE {user_badges_roles} (
    rid int(10) NOT NULL default '0',
    bid int(10) NOT NULL default '0'
    PRIMARY KEY (bid, rid),
  ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
  $ret[] = db_query("CREATE TABLE {user_badges_user} (
    uid int(10) NOT NULL default '0',
    bid int(10) NOT NULL default '0',
    type varchar(20) NOT NULL default ''
    PRIMARY KEY (bid, uid),
    KEY type(type),
  ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
  return $ret;
}

function user_badges_update_1() {
  $ret = array();
  update_convert_table_utf8('user_badges_badges');
  update_convert_table_utf8('user_badges_product');
  update_convert_table_utf8('user_badges_roles');
  update_convert_table_utf8('user_badges_user');
  $ret[] = update_query("ALTER TABLE {user_badges_badges} ADD PRIMARY KEY(bid), ADD INDEX bid_weight_name (bid, weight, name), ADD INDEX weight_name (weight, name)");
  $ret[] = update_query("ALTER TABLE {user_badges_product} ADD PRIMARY KEY (bid, nid)");
  $ret[] = update_query("ALTER TABLE {user_badges_roles} ADD PRIMARY KEY (bid, rid)");
  $ret[] = update_query("ALTER TABLE {user_badges_user} ADD PRIMARY KEY(bid, uid), ADD INDEX type (type)");
  return $ret;
}

Note the tables are also converted to utf8 (basically applies to user_badges_badges and user_badges_user but ran through all in case of future updates).

I added some more keys (though to the 6.x version - I'll post the schema so it can be seen clearly.

function user_badges_schema() {
  $schema = array();
  $schema['user_badges_badges'] = array(
    'description' => 'Holds the user badge images',
    'fields' => array(
      'bid' => array(
        'description' => t('Original badge ID'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'name' => array(
        'description' => t('Badge name'),
        'type' => 'varchar',
        'length' => 50,
        'not null' => TRUE,
        'default' => '',
      ),
      'image' => array(
        'description' => t('Associated image'),
        'type' => 'varchar',
        'length' => 80,
        'not null' => TRUE,
        'default' => '',
      ),
      'weight' => array(
        'description' => t('Order in list'),
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      ),
    ),
    'primary key' => array('bid'),
    'indexes' => array(
      'bid_weight_name' => array('bid', 'weight', 'name'),
      'weight_name' => array('weight', 'name'),
    )
  );
  $schema['user_badges_product'] = array(
    'description' => 'Holds the user badge images',
    'fields' => array(
      'bid' => array(
        'description' => t('Original badge ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0
      ),
      'nid' => array(
        'description' => t('Node ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array('bid', 'nid'),
    'indexes' => array(
      'bid_nid' => array('bid', 'nid'),
      'nid_bid' => array('nid', 'bid'),
    ),
  );
  $schema['user_badges_roles'] = array(
    'description' => 'Holds the user badge images',
    'fields' => array(
      'rid' => array(
        'description' => t('Original role ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'bid' => array(
        'description' => t('Original badge ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('bid', 'rid'),
    'indexes' => array(
      'bid_nid' => array('bid', 'rid'),
      'nid_bid' => array('rid', 'bid'),
    ),
  );
  $schema['user_badges_user'] = array(
    'description' => 'Holds the user badge images',
    'fields' => array(
      'uid' => array(
        'description' => t('Original user ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'bid' => array(
        'description' => t('Original badge ID'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'type' => array(
        'description' => t('Badge Type'),
        'type' => 'varchar',
        'length' => 20,
        'not null' => TRUE,
        'default' => '',
      ),
    ),
    'primary key' => array('bid', 'uid'),
    'indexes' => array(
      'bid_uid_type' => array('bid', 'uid', 'type'),
      'uid_bid' => array('uid', 'bid'),
    ),
  );
  
  return $schema;
}