MySQL vs PHP when getting a random item

Question

which is more effective (when managing more than 100 thousand records):

but. Mysql

SELECT * FROM user ORDER BY RAND(); 

Of course, after that I would have already had all the fields from this record.

B. PHP

use memcached to have $ cache_array store all data from "SELECT id_user FROM user ORDER BY id_user" for 1 hour or so ... and then:

$ id = array_rand ($ cache_array);

of course, after that I need to make a MYSQL call with:

 SELECT * FROM user WHERE id_user = $id; 

so ... which is more efficient? A or B ?

Answer 1

The right way to answer this question is to do a test. Perform quick and dirty runs every time, and then run test tests to determine which one works best.

Having said that ORDER BY RAND() is known to be slow because MySQL cannot use an index. MySQL will basically run the RAND() function once for each row in the table, and then sort the rows based on what came back from RAND() .

Your other idea is to save all user_id in memcached and then select a random element from the array, which might work better if memcached overhead was less than the cost of a full table scan. If your dataset is large, or the problem in the problem is the problem, you may run into problems. Also you add some complexity to your application. I would try to look for another way.

I will give you a third option that can exceed both of your suggestions: select the count(user_id) rows in your user table, and then create a php random number between 0 and the result of count(user_id) minus 1, inclusive. Then do a SELECT * FROM user LIMIT 1 OFFSET random-number-generated-by-php; .

Again, the right way to answer these questions is to navigate. Everything else is speculation.

Answer 2

The first one is incredibly slow because

MySQL creates a temporary table with all rows of results and assigns each one of which is a random sort index. Then the results are sorted and returned.

He described this blog post in detail.

Answer 3

 $random_no = mt_rand(0, $total_record_count); $query = "SELECT * FROM user ORDER BY __KEY__ LIMIT {$random_no}, 1";