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

Asked

Viewed 431 times

3

I had just asked questions regarding the ordering of random values through the MYSQL.

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

I use the Laravel 3. I went to check what might be slowing down the system.

Hence I obtained the following results in a query. In both cases, I am using LIMIT 10.

With the RAND():

 85.203170776367
 6.6289901733398

Without RAND()

0.074863433837891
0.11181831359863

How did the MYSQL uses the ORDER BY RAND() internally - to be so slow?

What possible changes to obtain results in the MYSQLrandomly.

Updating

I did more tests using the MYSQL directly with Phpmyadmin.

Look at.

With RAND():

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

Without RAND():

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

  • the numerical values are in milliseconds, certain?

  • In the source code of the Laravel is like this: (microtime(true) - $start) * 1000

  • Idependente of the measure, you can see the difference in performance. kkkkk

  • seriously it takes 85 seconds ? how many records does the table hold? 40 billion records ? rsrsrs Even using Rand(), you should not go from 0.4 in a small query to a table with 100,000 records and without any optimization.. all in default.

  • you want a random result (which can be repeated) or you always want a different result from the previous one?

  • Your table has 9024 records, right? Surely there is some problem in the table.. It may be using indices too much or the wrong way.. A consultation with ORDER BY RAND() limit 10; normally return in 0.04 seconds on a server with default settings, with no optimizations..

  • I’ll show you the appointment I’m doing. You’ll stand on your hair

  • 2

    Send us this berry to see!

  • Cry in that Paste bin

  • When the RAND is what gives a degree

  • I figured it was a little monster. rsrs has a lot of subselect, but that doesn’t mean it’s the problem.. can be misuse or lack use of indices or keys such as relationships (foreign key).. post the DDL of the tables involved.

  • 1

    Hey, guys? What’s new?

  • Yes. Ronaldinho Gaúcho came out of Fluminense, out of nowhere...

Show 8 more comments

1 answer

5

It takes time because it works in a way that needs to generate a random number for each row of the table. Then he sorts 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 lines to generate this random number. You can do this by limiting the number of lines you need, for example 1 number would be LIMIT 1, and divide by total table numbers,COUNT(*) and then multiply by 10 to avoid returning fewer lines than you need.

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

Upside: Easy to use in complicated queries and easy to adjust the number of lines needed: simply modify the LIMIT numeroDesejado.

Downside: the response time still depends on the number of lines you want to return.

I did not get to test, but according to the reference article for this answer improved a lot.

Reference: http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

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

Browser other questions tagged mysql random

You are not signed in. Login or sign up in order to post.