Just getting them in there, these are not original ideas, I got them from someone else, just wanted to post them on my blog for convenience sakes:
* Run a query to determine the MAX ID number in the table.
$MAX = SELECT COUNT(*) FROM tbl
/* Use PHP to generate a random number between 1, and the max
$ID = RAND(1, $MAX)
/* Run a last query to select the ID that is >= the random one.
SELECT * FROM tbl WHERE id >= $ID LIMIT 1
Another possible solution would be a heavy modification to that query. This solution is likely the best.
New Query:
SELECT *
FROM `tbl` AS `l1`
JOIN
(SELECT (RAND() * (SELECT MAX(`id`) FROM `tbl`)) AS `id`) AS `l2`
WHERE
`l1`.`id` >= `l2`.`id`
ORDER BY
`l1`.`id` ASC
LIMIT 1;
Share This Post On:
Wow! What a great solution to the order by rand problem! I always wondered how to get around order by rand issues, the performance of order by rand is just horrible! This is a fast and speedy work around!
Only works when you want one result. Back to the drawing board if you want more than one. Might be better to run in a loop, pseudo code:
$max = SELECT MAX(`id`) FROM `tbl`)
$results = array()
$numToGet = 100
while (count($results) < $numToGet)
SELECT *
FROM `tbl` AS `l1`
JOIN
(SELECT (RAND() * $max) AS `id`) AS `l2`
WHERE
`l1`.`id` >= `l2`.`id`
AND l1.id NOT IN ($results)
ORDER BY
`l1`.`id` ASC
LIMIT 1
INTO $results[]
end while