Why does ORDER BY RAND () leave the query slow? Is there another alternative?

Question

Why does ORDER BY RAND () leave the query slow? Is there another alternative?

Navigation

#1 by (5 votes)

3

I had just asked questions about ordering random values through MYSQL .

From there I began to notice an uncomfortable slowness in the system of friendships suggested here by the company.

I use Laravel 3 . I went to do a check of what could be slowing down the system.

Then I got the following results from a query. In both cases, I'm using LIMIT 10 .

With RAND() :

 85.203170776367
 6.6289901733398

No RAND()

0.074863433837891
0.11181831359863

How does MYSQL use ORDER BY RAND() internally - to stay so slow?

What possible changes to get results in MYSQL randomly.

Update

I did more testing using MYSQL directly with PhpMyAdmin.

See.

With RAND() :

 SELECT * FROM pessoa ORDER BY RAND()
 #A mostrar registos de 0 - 29 (9024 total, O Query demorou 1.0453 sec)

No RAND() :

SELECT * FROM pessoa ORDER BY id
#A mostrar registos de 0 - 29 (9024 total, O Query demorou 0.0008 sec)

mysql random

asked by anonymous 17.09.2015 / 15:57

1 answer

JavaScript with ajax, function return. Because it does not work? How do I display my highest code for each species?

score 5 · Answer 1

It takes time because it works in a way that needs to generate a random number for each row in the table. Then he orders these lines and returns one of them, according to these random numbers.

So the more rows your table has, the longer it will take.

An alternative would be to reduce the number of rows generating this random number. You can do this by limiting the number of rows you need, for example 1 number would be LIMIT 1 , and divide by the total number of the table, COUNT(*) and then multiply by 10 to avoid returning fewer rows than you need. p>

SELECT * FROM tabela WHERE RAND()<(SELECT ((1/COUNT(*))*10) FROM tabela) ORDER BY RAND() LIMIT 1;

Advantage : Easy to use in complicated queries and easy to adjust the number of lines required: just modify LIMIT numeroDesejado .

Disadvantage : The response time still depends on the number of rows you want to return.

I did not get to test, but according to the reference article for that response it improved a lot.

Reference: link

In this article he presents other alternatives with their advantages and disadvantages. But the one chosen as the best alternative was the one exposed here for its better efficiency and ease of use.