All geek questions in one place

When is the MySQL ORDER BY RAND () order order executed?

I read about ORDER BY RAND () and performance issues - do they only apply to queries that return large data sets? For example, if I have a table with 100,000 rows and return a data set with 10 records using the WHERE clause, then use ORDER BY RAND () LIMIT 1, will this ORDER BY RAND () AFTER my table has been applied filtered before records matching the WHERE clause, and therefore have minor performance issues?

+2
source share
4 answers

You are right, it will apply ORDER BY after reducing the number of rows with WHERE, GROUP BY and HAVING. But he will apply ORDER BY until LIMIT.

So, if you filter the number of rows down enough, then yes, ORDER BY RAND () can achieve what you want, without much impact on performance. There is a legitimate benefit for code that is simple and easy to read.

The problem arises if you think that your query should reduce the number of rows to something small, but over time, as your data grows, the number of rows to be sorted becomes large again. Since your query then does LIMIT 10 in the sorted result, it hides the fact that you are doing ORDER BY RAND () on 500k lines. You just see that performance is mysteriously declining.

SQL Antipatterns: :

+8

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

:

  • SELECT COUNT (*) FROM Table

  • 0 minus 1 /.

  • SELECT * FROM Table LIMIT random_number_here, 1

+2

, ORDER BY RAND() , WHERE, .

50 000 :

SELECT * FROM `mytable` LIMIT 1  (1 total, Query took 0.0007 sec)
SELECT * FROM `mytable` WHERE First = 'Hilda' LIMIT 1 (1 total, Query took 0.0010 sec)
SELECT * FROM `mytable` WHERE First = 'Hilda' (142 total, Query took 0.0201 sec)
SELECT * FROM `mytable` WHERE First = 'Hilda' ORDER BY RAND() LIMIT 1 (1 total, Query took 0.0229 sec)
SELECT * FROM `mytable` WHERE First = 'Hilda' ORDER BY RAND() (142 total, Query took 0.0236 sec)
SELECT * FROM `mytable` ORDER BY RAND() LIMIT 1 (1 total, Query took 0.4224 sec)
+1

RAND() , , LIMIT . - , , , .

:

http://jan.kneschke.de/projects/mysql/order-by-rand/

0

Source: https://habr.com/ru/post/1607045/

More articles:


All Articles