php - MySQL Random rows in neighbourhood -

this question has answer here:

• doing while / loop 10 random results 3 answers

i have table (persons) 25m rows:

id int(10) pk points int(6) index other columns 

i want show user 4 random rows close each other in points. found query after searching , tuning generate random rows impressive fast:

select person_id, points persons r1 join        (select (rand() *                      (select max(person_id)                         persons)) id)         r2  r1.person_id>= r2.id , points > 0  order r1.person_id asc  limit 4 

so query in php. gives me great , fast results (below 0.05 seconds when warmed up). these rows random (with @ least 1 point since points > 0). show rows little bit close, doesn't have every time, let's query limit 50 , select random row in php , 3 closest rows (based on points) next it. think need sort result, pick random row , show rows after/before it. have no idea how can make this, since quite new php.

anyone suggestions, feedback welcome :)

build index on points column (if not exist), perform randomisation logic on that:

alter table persons add index (points);  select   person_id, points     persons join (            select rand() * max(points) pivot              persons             points > 0          ) t on t.pivot <= points order points limit    4 

note approach select pivot using uniform probability distribution on range of points values; if points non-uniform, can end pivoting on values lot more others (thereby resulting in seemingly "non-random" outcomes).

to resolve that, can select random record more uniformly distributed column (maybe person_id?) , use points value of random record pivot; is, substitute following subquery in above statement:

           select   points pivot                persons join (                        select floor(                                min(person_id)                              + rand() * (max(person_id)-min(person_id))                              ) random                         persons                       where  points > 0                      ) r on r.random <= person_id               points > 0            order person_id            limit    1