php - Why is MySQL returning the same results while using RAND() in the SELECT statement?

Question

I have a number of browser windows open pointing to the same auto-refreshing PHP page. It accesses a MySQL database to identify customer information that is out of date. Specifically getting records that haven't been updated in the last day and forces an update. The rest of the code seems to be processing fine.

Here is my MySQLi query:

$query = "SELECT *
          FROM customers
          WHERE customer_group='consumables' AND customer_updated < DATE_SUB(NOW(), INTERVAL 1 DAY)
          ORDER BY RAND()
          LIMIT 10";

I have been informed that RAND() is not very suitable due to it's slow processing of large tables, but my tables will not increase to over 20000 before the end of this project. I also have a random variable being passed to the URL like "clientdataupdates.php?nocachepls=1541231".

So here is my problem: Out of the current 5000 odd records, if this script is run in multiple browser windows at the same time, they are getting the same records returned from MySQL. Admittedly the chosen record seems to be picked at random, but the same record is returned in all of the windows if the query is run at the exact same time.

My research has been quite limited by the fact that they keywords I have been searching for (over a few days now) seem to relate to other problems e.g. "php mysql returning same result while using rand()" has too many google responses that point to using rand() in general.

Whilst I would still appreciate any assistance, I would actually more like to know why this is happening. My knowledge of the inner workings of MySQL is limited, but for all my experience interfacing PHP and MySQL I have not seen anything similar occur either.

UPDATED:

I have also tested using an ajax function that includes a callback function to kick it off again. Every time the div contents are the same record - but it still looks like which record is selected at random.

<div id='worker1' class='workerDiv'>worker: waiting..</div>
<div id='worker2' class='workerDiv'>worker: waiting..</div>
<div id='worker3' class='workerDiv'>worker: waiting..</div>
<div id='worker4' class='workerDiv'>worker: waiting..</div>
<div id='worker5' class='workerDiv'>worker: waiting..</div>
<script>
 function nextWorker(thisWorker){
  setTimeout(function(){ ajaxpage('customerdata_worker.php',thisWorker,nextWorker(thisWorker)); }, 10000);
 }
 setTimeout(nextWorker('worker1'), 100);
 setTimeout(nextWorker('worker2'), 100);
 setTimeout(nextWorker('worker3'), 100);
 setTimeout(nextWorker('worker4'), 100);
 setTimeout(nextWorker('worker5'), 100);
</script>

Answer 1

Rand() seed

MySQL uses the system clock to seed RAND() when there is no second value. The seed value is in microseconds, and I can not reproduce the problem of RAND() producing the same value twice as you describe.

If you open MySQL Workbench and execute two statements at the same time. The output is different for each.

SELECT RAND();
SELECT RAND();

When you open multiple tabs and get the same results. It is likely a caching issue, but you state you're stamping the URL to prevent caching. So enable SQL logging on the server and verify that new queries are being called.

Rand() Performance

ORDER BY RAND() is slow because it requires MySQL to read the entire table. Even ORDER BY RAND() LIMIT 1 still requires MySQL to read the entire table.

UPDATE:

You can see what the random value is that SQL is generating.

$query = "SELECT *, RAND() AS `X`
          FROM customers
          WHERE customer_group='consumables' AND customer_updated < DATE_SUB(NOW(), INTERVAL 1 DAY)
          ORDER BY `X`
          LIMIT 10";

That will include the column X for each row. The random value used to order the query. Add this to the output and see if each browser is truly returning the same result sets from MySQL.