ORDER BY RAND() pagination issue

[1internet]

So I want to show my results in random order, I have used the ORDER BY RAND() mysql function, but the issue is, when you have pagination and go to another page, every page is also random results, i.e. there could be the same results as the previous page, and by clicking through all pages, you will never see all results.

My query is

SELECT * FROM `businesses` ORDER BY RAND() LIMIT $startResults, $perPage

 

So how do I make all the results random, but not sure duplicate results on the other pages?

[Christian F.]

You have to save the results of the entire query to another (temporary) location, and use that for the basis of the pagination.

The simplest way you could do this, is to just serialize () the array with the results from the query (without the LIMIT clause), and then save it to a file or temporary table. However you control access to that file/table, say one per user, for instance, or how long it should be available, is completely up to you.

 

There are also other, more complicated, methods to solve this. Methods which may be more suited to the task if we're talking about a lot of data, but generally this should serve pretty well.

Edited February 3, 2013 by Christian F.

[requinix]

RAND() allows you to seed the generator. Pick a random number, save that somewhere, and use it every time.

$query = "SELECT * FROM `businesses` ORDER BY RAND($random) LIMIT $startResults, $perPage";

 

[edit] This is fine if you don't have too many results; this query is bad and will take a relatively long time so if not then storing the results in a table (with some identifying information so you know whose results they are) is better.

Edited February 4, 2013 by requinix

[P5system]

Take the results in an array. An use array pagination it will work

[requinix]

Take the results in an array. An use array pagination it will work

Unless there are 1M rows to consider. Or even 10K.

 

[edit] Shuffling the array, which you didn't say but would be a fundamental requirement to the solution, doesn't help. It's the same problem: it'll be random every single time. srand() might affect shuffle(), I don't know, but if so that would be a solution to that... except if you're seeding the RNG you might as well do it in MySQL where it'll perform better.

Edited February 5, 2013 by requinix

[1internet]

requinx - if I use the $random, does that mean this variable is a random number, that is then stored within the session?

[Christian F.]

While I'm not requinix: Yes, that's what it means. If it's not set in the session variable, then generate it, save it, and then send it to the query.

[Psycho]

This is really more complicated than you think. If the pagination order is supposed to be random how would it handle new/removed records?

 

So, I'm on page 2 and someone removes a record that would have been on page 3. What happens when I navigate to Page 3? Depending on the method chosen above, the result could be all over the place. Page 3 has 1 less record than it should, the entire record set could be completely re-randomized (even with a seed value), empty row int he grid, etc. etc. And the same problems could arise if someone adds a record.

[Jessica]

Who would bother to look past page 1 of a result set with 10k rows in random order, without applying a logical order?

[Christian F.]

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well. Even if he went for the temporary storage solution, he could still write code that deleted the record from there as well.

The ordering of the results aren't of any consequence for the number of rows the query returns, after all. Not unless you save the page number for each record on the first query. Which, I agree, would be quite silly.

[requinix]

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well.

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.

mysql> create table test (a int);
Query OK, 0 rows affected (0.12 sec)
 
mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(,(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|   10 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
10 rows in set (0.06 sec)
 
mysql> delete from test where a=10;
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
9 rows in set (0.03 sec)
 
mysql> delete from test where a=5;
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    7 |
|    4 |
|    9 |
|    2 |
|    3 |
|    6 |
|    1 |
|    8 |
+------+
8 rows in set (0.04 sec)

[Psycho]

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.

 

Right. What I was getting at is that if the order is "random" there is no logical way to handle adds/deletions to the source data as you navigate from page to page. So, if using the seed method, a record was deleted from page 2, then all the records on page 3, 4, etc. would change.

 

You could build some custom functionality such as getting a random list and storing it is session/cookie data. Then, if a record is deleted, all the records after that record would move up in position. And, if records are added, the only logical thing to do would be to add them to the end of the list (which really isn't random). If you are going to create some fixed list to start such as this, you definitely need to account for add/deletes otherwise you may get less records than intended on a page and new records won't display in the data.

 

Believe me, I have had to deal with many different issues regarding pagination. I've had product managers and designers come up with plenty of ideas that sound good on paper but cannot logically be implemented (e.g. subtotals). When these issues are identified the typical response is to add more complexity to handle those issues - which leads to more bugs. Some may argue that add/deletes while a user is paginating through the records is an edge case scenario and does not need to have a lot of additional work to make it "perfect". However, my position is that whatever feature is being implemented should "just work". A randomized pagination doesn't "just work". And, personally, as a user, my first instinct would be to sort the grid in some logical order - but that my be due to the fact that I have a slight OCD tendency.

[fenway]

Not that I understand why you need this -- but depending on what you're actually doing, you can use some sort of function to apply, seeded for each user/timestamp/whatever, which you apply to the PK.

[1internet]

making the argument for rand() a random number that was set in the session seemed to work.