SELECT u.id, u.honour, COUNT(*) + 1 AS rank
FROM user_info u
INNER JOIN user_info u2
  ON u.honour < u2.honour
WHERE u.id = '$id'
  AND u2.status = 'Alive'
  AND u2.rank != '14'

This query is currently utterly raping my server. It works out based on your honour what rank you are within the 'user_info' table which stores it out of all our users.

Screenshot for explain.

http://cl.ly/370z0v2Y3v2X1t1r1k2A

SELECT u.id, u.honour, COUNT(*)+1 as rank
FROM user_info u 
    USE INDEX (prestigeOptimiser)
INNER JOIN user_info u2 
    ON u.honour < u2.honour
WHERE u.id='3'
    AND u2.status='Alive'
    AND u2.rank!='14'