Getting random data from a MySQL database but not repeating data

You might consider a pagination-like solution. Rather than ordering by RAND() (not good form a performance standpoint anyway), why not simply use LIMIT clause and randomize the offset.

For example:

SELECT product_id FROM products ORDER BY product_id LIMIT X, 1

Where X is the offset you want to use. You could easily keep track of the offsets used in the application and randomize amongst the available remaining values.

PHP code to call this might look like this:

if(!isset($_SESSION['available_offsets'] || count($_SESSION['available_offsets']) === 0) {
    $record_count = ...; // total number of records likely derived from query against table in question
    // this creates array with numerical keys matching available offsets 
    // we don't care about the values   
    $available_offsets = array_fill(0, $record_count, '');  
} else {
    $available_offsets = $_SESSION['available_offsets'];
}
// pick offset from available offsets
$offset = array_rand($available_offsets);
// unset this as available offset
unset($available_offsets[$offset]);
// set remaining offsets to session for next page load
$_SESSION['available_offsets'] = $available_offsets;

$query = 'SELECT product_id FROM products ORDER BY product_id LIMIT ' . $offset . ', 1';
// make your query now

You could try adding a seed to the RAND to avoid repeats

select * from products where product_id <> previous_product_id order by rand(7) limit 1;

From http://www.techonthenet.com/mysql/functions/rand.php :

The syntax for the RAND function in MySQL is:

RAND( [seed] ) Parameters or Arguments

seed

Optional. If specified, it will produce a repeatable sequence of random numbers each time that seed value is provided.

First, unclear what scripting language you will use to piece this together, so will answer conceptually. I also added uppercase to your MySQL for readability. So first let’s look at this query:

SELECT * FROM products WHERE product_id <> previous_product_id ORDER BY RAND() LIMIT 1;

Superficially does what you want it to do, but if your database has thousands of items, RAND() is not recommended. It defeats all MySQL caching & is a resource hog. More details here, specially the area that reads:

A query cannot be cached if it contains any of the functions shown in the following table.

That’s just not good.

But that said you might be able to improve it by just returning the product_id:

SELECT product_id FROM products WHERE product_id <> previous_product_id ORDER BY RAND() LIMIT 1;

And then you would have to do another query for the actual product data based on the product_id, but that would be much less taxing on the server than grabbing a whole set of randomized data.

But still, RAND() inherently will bog down your system due to lack of caching. And the problem will only get worse as your database grows.

Instead I would recommend having some kind of file-based solution. That grabs a random list of items like this:

SELECT product_id FROM products WHERE product_id <> previous_product_id ORDER BY RAND() LIMIT 0,100;

You will strictly be grabbing product ids and then saving them to—let’s say—a JSON file. The logic being is as random as you want this to be, you have to be realistic. So grabbing a slice of 100 items at a time will give a user a nice selection of items.

Then you would load that file in as an array and perhaps even randomize the array & grab one item off of the top; a quick way to assure that the item won’t be chosen again. I you wish you could shorten the array with each access by re-saving the JSON file. And then when it gets down to—let’s say—less than 10 items, fetch a new batch & start again.

But in general you RAND() is a neat trick that is useful for small datasets. Anything past that should be re-factored into code where you realistically leverage what you want users to see versus what can realistically be done in a scalable manner.

EDIT: Just another thing I noticed in your code on keeping track of product IDs. If you want to go down that route—instead of saving items to a JSON file—that would be fine as well. But what you are describing is not efficient:

+--------------------------------------------------------------------------------------------+                                                                                          
|user_id |timestamp         | product_seen_string                                            |
|--------------------------------------------------------------------------------------------|
|foo     |01-01-14 12:00:00 |product_id_1 AND product_id_2 AND product_id_3 AND product_id_n |
+--------------------------------------------------------------------------------------------+

A better structure would be to simply store the IDs in a MySQL table like this; let’s call it products_seen:

+----------------------------------------------------+                                                                                          
|user_id |timestamp         | product_seen_id        |
|----------------------------------------------------|
|foo     |01-01-14 12:00:00 |product_id_1            |
|foo     |01-01-14 12:00:00 |product_id_2            |
|foo     |01-01-14 12:00:00 |product_id_3            |
+----------------------------------------------------+                                                                                          

And then have a query like this

SELECT * FROM products, products_seen WHERE user_id = products_seen.user_id product_id <> product_seen_id ORDER BY RAND() LIMIT 1;

Basically, you would be cross referencing products_seen with products and making sure the products already seen by user_id are not shown again.

This is all pseudo-code, so please adjust to fit the concept.