[SOLVED] Select random rows not doing math right

[netstormx]

SELECT * FROM games AS r1 JOIN (SELECT MAX(gameid) as wid_c FROM games) as tmp1 WHERE r1.gameid >= (SELECT (RAND() * tmp1.wid_c) AS id) LIMIT 0,3

 

Thats what I have, and I have around 12000 database entries. It seems to only be showing 3 entries at the bottom range up to the 400th entry or so.

 

Any clues?

[netstormx]

I've tried this as well, and still the lower entries only show up for some reason.

 

select * from games where gameid >= (select floor(rand() * ((select max(gameid) from games) - (select min(gameid) from games)) + (select min(gameid) from games))) order by gameid limit 3;

 

 

[ignace]

Try:

 

SELECT * FROM games ORDER BY rand() LIMIT 3

[netstormx]

Figured it out

 

SELECT * FROM games AS r1 JOIN (SELECT MAX(gameid) as wid_c FROM games) as tmp1 WHERE r1.gameid >= (SELECT (RAND() * tmp1.wid_c) AS id) order by rand() LIMIT 0,3

 

Just needed the extra order by rand() in there. I'm pretty tired =/.

 

The reason I did this rather than the code you provided is my database is huge. This code doubles the speed, and is less harsh on server load.

[ignace]

This code doubles the speed, and is less harsh on server load.

 

How is it that your code can be double as fast as you do the same thing I did you have both: SELECT * FROM games and ORDER BY rand() LIMIT 3 plus you throw in a join with a subquery SELECT max(..) which means it will go over each row until it has found the key with the highest value. IMO this code does the opposit of what you claim.