php - Fastest MySQL row rank of big table -

info: have table (persons):

person_id   int(10) points      int(6) 4 other columns of type int(5 or 6)

the table consist of 25m rows , growing 0.25m day. distribution of points around 0 300 points , 85% of table has 0 points.

question: return user rank he/she has if got @ least 1 point. how , fastest way it, in sql or php or combination?

extra info: lookups can happen every second 100 times. solutions have seen far not fast enough, if more info needed please ask.

any advice welcome, understand new php , mysql :)

create index on t(points) , on t(person_id, points). run following query:

select count(*) persons p p.points >= (select points persons p p.person_id = <particular person>)

the subquery should use second index lookup. first should index scan on first index.

sometimes mysql can little strange optimization. so, might better:

select count(*) persons p cross join      (select points persons p p.person_id = <particular person>) const p.points > const.points;

this ensures lookup points given person happens once, rather each row.

KHS