Random order in query over multiple pages

"order by rand()" not enough

[louponne]

I have a site where I list a few dozen records, 10 per page. I want to list them randomly. As I understand it, if I do

SELECT foo,whatever FROM tablename WHERE whatever=10 ORDER BY RAND() LIMIT 0,10;

I'll get 10 of them in random order. But then if I move on to page 2 of results, and do the same thing, I'll just get 10 more in random order - no way of being sure not to have some from the first 10.

It doesn't have to be total random - I mean the order could change, say, from one day to the next.

Anyone have an idea to do this?

[dmorison]

When you say the order can change just from one day to the next, do you mean that if person uses your application twice in one day, it doesn't matter that the results come in the same order - as long as tomorrow they are in a different order?

If so, I would set-up a process to run at, say, midnight, and fix the order for the next day - simply by selecting all records ordered by rand, and then doing an update on every record in the order they were returned setting some "sequence" value incrementally.

Then during the day you simply select ordering by sequence.

[louponne]

Thanks for your quick answer!

I would set-up a process to run at, say, midnight

the site hosted on a shared server, and I don't have access to setting up a daily job on it.

But yes, it doesn't matter to me that the same person would see the same order if he/she visits the site twice on a given day.
Well, actually, I'd prefer totally random, but that seemed to me very complicated to produce - whereas it seems to me that there should be a way I could generate a different set of random numbers depending on the day, and order my results based on that number (?) And, as I say, I would be satisfied with a different order each day.

[Fischerlaender]

I just tried this with a simple table, but it should solve your problem:
SELECT * FROM table ORDER BY MD5(concat(id,"2003-04-19","123.321.45.54")) LIMIT 10

The idea is simple: You order by an MD5-Hashvalue. You'll use your PRIMARY KEY (in this example: id) and some other strings, which are constant during a user session. (here: the date of today and the IP number of the visitor)

The order of the output is determined by the constant strings; this means, if you change one of them, the order will competely change. But as long as these strings are constant, the order is maintained, so that you can access the following ten lines with ... LIMIT 10,10.

The bad thing of this idea is that it is quite time consuming to calculate the MD5 value. On my test server (Celeron 800) it takes 1.10 seconds to order 66.000 lines. If your table is much smaller than this shouldn't be a problem.

[louponne]

Terrific idea, Fischerlaender - thanks!
My table is indeed much smaller - 300 records now, problably max of 500. But I'll also be running a condition or two in the query. Does this sound doable?

The bad thing of this idea is that it is quite time consuming to calculate the MD5 value

Well, this goes several steps beyond my MySQL knowledge - though I did look up MD5() in the manual! :)
Is there a simpler math function that I could use that would be less server-demanding?

[Fischerlaender]

MD5 is a hash function. A hash is a one-way-function that can be used to encrypt strings: if string1 and string2 are different, then md5(string1) <> md5(string2). If string1 == string2, then md5(string1) == md5(string2).

Even if string1 and string2 are just differing in a single character, then their md5 value will be completely different.

mysql> select md5("abcdef"),md5("abcdeg"); 
+----------------------------------+----------------------------------+ 
¦ md5("abcdef")     ¦ md5("abcdeg")     ¦ 
+----------------------------------+----------------------------------+ 
¦ e80b5017098950fc58aad83c8c14978e ¦ 75eeb2d5e87b9f02cd7093e604ab11e9 ¦ 
+----------------------------------+----------------------------------+

if you just have several hundred rows to calculate the MD5 value for, there is no problem with performance.
Even for 5000 rows it took just 0.06 seconds to calculate and order the rows.

[louponne]

Great! Many thanks again, Fischerlaender, this is a perfect solution then! :-)