Why is MySQL returning the same results while usin

问题:

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>

回答1:

You are probably receiving information from the MySQL query cache in some result sets.

Try this:

SELECT SQL_NO_CACHE *
       /* etc */

BEWARE: Put the SQL_NO_CACHE word on the same line as the SELECT and the * (or the name of the first column you are selecting).

See this: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html It says,

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

Pro tip: Avoid SELECT * in software. Give the names of the columns you need in the result set.

回答2:

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.

回答3:

You are not limiting the amount of records, so he's only bringing in different orders, but the same results attending your where. Try limit your results

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

回答4:

Not quite sure, but since you have no LIMIT in your query, I think it can be good idea to remove that really slow ORDER BY RAND() part from your query and simply use php function shuffle on result of mysql query.