mysql> explain SELECT v1.character_guild,v1.character_realm,v1.character_region,v1.character_faction
    ->      , SUM(v1.character_level) total
    ->   FROM (
    ->         SELECT t1.character_name
    ->              , t1.character_faction
    ->              , t1.character_realm
    ->              , t1.character_region
    ->              , t1.character_guild
    ->              , t1.character_level
    ->              , COUNT(t2.character_hash) cnt
    ->           FROM armory_ranking t1
    ->           LEFT JOIN armory_ranking t2
    ->             ON (t1.character_level,t1.character_hash) <= (t2.character_level,t2.character_hash)
    ->            AND t1.character_guild = t2.character_guild
    ->          WHERE t1.character_guild <> ''
    ->          GROUP BY t1.character_hash
    ->          HAVING cnt <= 25
    ->        ) v1
    ->  GROUP BY v1.character_guild
    ->  ORDER BY total DESC
    ->  LIMIT 10;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  576 | Using temporary; Using filesort              |
|  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1520 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL | 1520 |                                              |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set (4.45 sec)