mysql score表,大表上的MYSQL SELECT随机在ORDER BY SCORE

I have a large mysql table with about 25000 rows. There are 5 table fields: ID, NAME, SCORE, AGE,SEX

I need to select random 5 MALES order BY SCORE DESC

For instance, if there are 100 men that score 60 each and another 100 that score 45 each, the script should return random 5 from the first 200 men from the list of 25000

ORDER BY RAND()

is very slow

The real issue is that the 5 men should be a random selection within the first 200 records. Thanks for the help

解决方案

so to get something like this I would use a subquery.. that way you are only putting the RAND() on the outer query which will be much less taxing.

From what I understood from your question you want 200 males from the table with the highest score... so that would be something like this:

SELECT *

FROM table_name

WHERE age = 'male'

ORDER BY score DESC

LIMIT 200

now to randomize 5 results it would be something like this.

SELECT id, score, name, age, sex

FROM

( SELECT *

FROM table_name

WHERE age = 'male'

ORDER BY score DESC

LIMIT 200

) t -- could also be written `AS t` or anything else you would call it

ORDER BY RAND()

LIMIT 5