I'm trying to randomise a certain number of rows but lets say there are only 4 rows in the database and i need to get 6 random rows I want the possibility (even if there are more than 6 rows in the table) to produce duplicate rows.
Is this easily achieved in mySQL ?
My current query is like this:
SELECT * FROM winners ORDER BY RAND() LIMIT 6
The idea is a user can win more than once. :)
Hope you can help! !
Any solution involving ORDER BY RAND() is frowned upon, because it can't use an index and it basically sorts the whole table (which may grow very large) just to pick one row.
The better solutions involve generating a random number between MIN(id) and MAX(id) and that's your chosen random row. As your table gets larger, this becomes a bigger and bigger advantage.
It's so much more efficient to pick a random ID, that I'd recommend just picking six random ID's one at a time, and then looking up those rows one at a time. Therefore you have a chance of picking a given row more than once.
If you aren't guaranteed that all your ID's are consecutive, you can pick the first ID that is greater than the random pick. So in pseudocode:
$MIN, $MAX = SELECT MIN(ID), MAX(ID) FROM winners
FOR LOOP FROM 1 to 6
$R = $MIN+RANDOM($MAX-$MIN)
$WINNER[] = SELECT * FROM winners WHERE id >= $R LIMIT 1
id 5 ?LIMIT 1 OFFSET 5 to choose by position, but you'll find that choosing a row by value (aided by an index) is far better for performance.SELECT * FROM winners ORDER BY RAND() LIMIT 1
UNION ALL
SELECT * FROM winners ORDER BY RAND() LIMIT 1
UNION ALL
SELECT * FROM winners ORDER BY RAND() LIMIT 1
UNION ALL
SELECT * FROM winners ORDER BY RAND() LIMIT 1
UNION ALL
SELECT * FROM winners ORDER BY RAND() LIMIT 1
UNION ALL
SELECT * FROM winners ORDER BY RAND() LIMIT 1
Assuming you have at least one row, you can multiply the number of rows and then return randomly from that enlarged set:
SELECT w.*
FROM winners w cross join
(select 1 as n union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6
) nums
ORDER BY RAND()
LIMIT 6;
cross join is multiplying the number of rows, by six in this case (because there are six rows in the nums table). It then randomly selects from those enlarged results.
ORDER BY RAND() is O(n log n), this solution is O(6n log 6n), while my solution is O(6(n log n)), right?winners were a complex query). This is sorting six times the original data, whereas yours is sorting the original data six times (these have the same complexity because constant factors are ignored in complexity calculations).This questions sounds like it could be the XY Problem. It sounds like you might be asking about a solution to your problem rather than your problem. See: https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem
I think it might be better to turn four rows into six in your application rather than selecting duplicate rows.
create duplicates in the database is the complete opposite of an efficient database structure ;)