How to select random unique records each time an SQL query is executed

Question

I have a table "masterurls", it has more than 1 million records. I want to get random records every time I execute a query. He should not have any records that were received in previous performances. I already have this query:

SELECT m.url FROM masterurls ORDER BY RAND() LIMIT 200

The problem is that the above query returns only the first 200 hundred records and randomizes them each time.

Answer 1

Since you can pass the seed parameter to the RAND() function, you can “break” into random results by creating a seed before the first page.

Code example: For the first page (depending on the language):

 int seed = Math.abs(new Random().nextInt()); 

SQL query:

 SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200; 

Store the seed somewhere (for web applications you can use a parameter or url session). For the following pages:

 SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200 * {pageNumber}, 200; 

Note. Sorting by RAND() is a tough operation, you might be better off storing an indexed column with a URL hash code, and then use modular or other random functions.

Answer 2

How do you know if the URL was previously accessed. My best suggestion is to set a flag to know this on the table. Add a field similar to the view in the table, which will take two values ​​1 or 0, 1 for the already available and 0 for access. Then you can use

 SELECT m.url FROM masterurls m WHERE view='1' ORDER BY RAND() LIMIT 200;