MySQL: Random pick that lasts for a specific period of time?

Question

Okay... I'm lost (again). How can I make a custom seed for rand() that will keep the pick from the database for a specific period of time: 10 minutes, 1 hours, 1 day, etc.

I use this:

$query = 'SELECT * FROM table order by rand () LIMIT 1';

To get a random pick every time I refresh the page. I've been reading for days, but I just can't find an example that explains how to make a custom seed that will keep the selection for a specific period of time. Please help me... sighs

Answer 1

Once you have selected it, you need to store that data into the session and session can be set to the time you specify. No need to query to the database every time. And secondly query has nothing to do with setting the time for an ad/whatever for specified time.

Answer 2

ORDER BY RAND() is a really bad idea - it kills database performance.

This is the sort of thing you should run on a cron job as often as you want the "random pick" to last, and store the ID of the row that is your random pick of the minute/hour/day. You'll need to watch out to make sure that row doesn't get deleted though.

Answer 3

"Create a custom seed"

I believe the question wants you to do something like this:

$query = 'SELECT * FROM table order by rand (' . get_seed() . ') LIMIT 1';

<?php
// return the current hour.  so the seed is the same each hour
function get_seed() {
  return date('H');
}
?>

The purpose of a 'seed' is to create the same random sequence for testing purposes.

RAND(N) If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

Answer 4

You could create a temp table:

SELECT * INTO #temp FROM table ORDER BY rand() LIMIT 1

Then query the temp table to display your data. Drop/re-create the table after your timeout.

Answer 5

If you're working with PHP, you can make an algorithm, which generates a random number (using rand()) and then save this number somewhere in the database or file. Then check the current date and the date of generation of the random number. Calculate the difference and use an if statement to determine, whether it's time to generate random number again. Here's some pseudo-code (note that by date I mean a timestamp or Unix time):

$gendate = getGenerationDateFromDB();
$now = getCurrentDate()
if(getDifference($gendate, $now) > [time interval]) then
  $randnum = generateRandNumber();
  saveRandNumberInDB($randnum);
  saveGenDate();
else
  $randnum = getRandNumberFromDB();

Answer 6

I have an idea for the concept, not a formal solution:

Use md5() on the down-rounded UNIX_TIMESTAMP()/60. You have a number of records in the table, let it be X. Calculate a number from the md5(), let it be Y. Calculate the Y % X, let it be Z. Use limit 1 offset Z at the end of the SQL.

Answer 7

As Dominic Rodger said - order by rand is horrible. But I find using cron too big a gun for the job. (especially that You don't know how)

You should rather generate a random integer number in php and store it and pass it to the mysql to something like that:

SELECT * FROM table WHERE id=(MOD('.$php_generated_random.', LAST_INSERT_ID())+1)

it gets a big random number and does modulo, so that it doesnt go over the table length.

This has a problem with LAST_INSERT_ID() though. Your mysql may return 0 there in many cases.

So the good'n'safe way to do that would be:

1. check if it's time to generate new random
2. if so, then get the max id from table (assuming that table length changes sometimes)
3. generate a random number to be an id
4. store the id and generation time

and do only select where id=$sth