MYSQL SELECT random on large table ORDER BY SCORE

Question

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

score 2 · Answer 1 · answered Aug 18 '14 at 10:33

2

I dont think that sorting by random can be "optimised out" in any way as sorting is N*log(N) operation. Sorting is avoided by query analyzer by using indexes.

The ORDER BY RAND() operation actually re-queries each row of your table, assigns a random number ID and then delivers the results. This takes a large amount of processing time for table of more than 500 rows. And since your table is containing approx 25000 rows then it will definitely take a good amount of time.

answered Aug 18 '14 at 10:33

Rahul Tripathi

168,305
31
280
331

Thanks @r-t Do you have a proposed solution ? – Enstine Muki Aug 18 '14 at 22:31
@EnstineMuki:- The solution given by John Rudell is good. I think you can go with that :) – Rahul Tripathi Aug 19 '14 at 04:31

John Ruddell · Accepted Answer · 2014-08-20T20:58:46.367

2

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

edited Aug 20 '14 at 20:58

answered Aug 18 '14 at 13:18

John Ruddell

25,283
6
57
86

Hi @john-ruddell I'l surely try this out but let me find out from r-t if he has an option – Enstine Muki Aug 18 '14 at 22:31
@EnstineMuki try it out and let me know if its faster :) I believe it should do what you want :) – John Ruddell Aug 18 '14 at 22:35
I'll surely update @john-ruddell – Enstine Muki Aug 18 '14 at 23:38
Hey @John Ruddell I'm coming back to work on this. Is the t in that query a typo? Does it have it place? – Enstine Muki Aug 20 '14 at 20:50
@EnstineMuki you MUST have the t there... it will error if you dont... every table must have an alias and thats the alias i gave that table – John Ruddell Aug 20 '14 at 20:57
Yeah that's correct @John Ruddell. I think it's working great – Enstine Muki Aug 20 '14 at 21:09
@EnstineMuki does it speed things up? – John Ruddell Aug 20 '14 at 21:09
@EnstineMuki also if it does solve your problem i'd greatly appreciate it if you marked it as accepted :) – John Ruddell Aug 20 '14 at 21:46

MYSQL SELECT random on large table ORDER BY SCORE

ORDER BY RAND()

2 Answers2

Linked