Geek Answers Handbook

Why does MySQL return the same results when using RAND () in a SELECT statement?

I have several browser windows that point to the same refreshing PHP page. He accesses the MySQL database to identify outdated customer information. In particular, receiving records that were not updated on the last day and forcibly updated. The rest of the code seems to be handling 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 was told that RAND () is not very suitable due to the slow processing of large tables, but my tables will not grow to more than 20,000 until the end of this project. I also have a random variable that is passed by URL, for example "clientdataupdates.php? Nocachepls = 1541231".

So, here is my problem: from the current 5000 odd entries, if this script is run simultaneously in several browser windows, they get the same entries returned from MySQL. Of course, the selected record is apparently randomly selected, but the same record is returned in all windows if the query is executed at the same time.

My research was quite limited by the fact that the keywords I was looking for (within a few days) seemed to relate to other issues, for example. "php mysql returns the same result when using rand ()" has too many google answers that indicate the use of rand () in general.

, , . MySQL , PHP MySQL .

:

, ajax, , . , div , , .

<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>
+4
4

, MySQL .

:

SELECT SQL_NO_CACHE *
       /* etc */

BEWARE: SQL_NO_CACHE , SELECT, * ( , ).

: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html :

SELECT , . , , . , , , .

Pro tip: SELECT * . .

+1

Rand() seed

MySQL RAND(), . , RAND(), , .

MySQL Workbench . .

SELECT RAND();
SELECT RAND();

. , , , URL-, . SQL , .

Rand()

ORDER BY RAND() , , MySQL . ORDER BY RAND() LIMIT 1 , MySQL .

UPDATE:

, SQL.

$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";

X . , . , MySQL.

+2

, LIMIT , , ORDER BY RAND() php shuffle mysql.

+1

, , , . .

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

More articles:


All Articles