How to have optimized compound select for a range defined criteria?

Keywords´╝Ü mysql sql select query-optimization nested-queries

Question: 

I have in the code two selects, first randomly selecting any 20 sportsmen

db.Query("SELECT sp_no, first_name FROM sportsmen LIMIT ?,20", rand.Intn(100000))

Then for every returned sportsmen in a loop do another select from points table where published their results in different competitions

db.Query("SELECT max(point) FROM points WHERE sp_no =" + spNo)

In result I have 20 sportsmen with their first_name and highest points numbers. I'm looking for optimisation of these selects to combine them into one for avoiding looping in a code and then measure performance.

How to have one select statement which returning first_name and their highest points?

Looks like it's combination compound selects, join and exists verification?

Thank you.

Answers: 

We can try aggregating by sp_no, and ordering randomly:

SELECT sp_no, first_name, MAX(point) AS max_point
FROM sportsmen
GROUP BY sp_no, first_name
ORDER BY RAND()
LIMIT 20;

Note that this approach completely handles all the heavy lifting directly in MySQL. You app code should only have to execute the above query and fetch the result set.

You could join a table expression with the second query, as in:

select
  x.sp_no, x.first_name, max(point) as max_points
from (
  select sp_no, first_name
  from sportsmend 
  order by sp_no
  limit ?, 20
) x
join points p on p.sp_no = x.sp_no
group by x.sp_no, x.first_name