Geek Answers Handbook

MySQL RAND (), how often can it be used? does he use / dev / random?

I have a table with several rows (vertices 50), I need to get a random value from the table, I can do this using ORDER BY RAND() LIMIT 1
The main question is that when I have 6k selects in 5 seconds, rand stil is 'reliable'? How is the rand calculated, can I sow it over time? (idk, every 5 seconds).

+4
source share
3 answers

MySQL pseudo random number generator is completely deterministic. The docs say:

RAND () is not intended for a perfect random generator. This is a quick way to generate on-demand random numbers that carry across platforms for the same version of MySQL.

It cannot use / dev / random, because MySQL is designed to work with various operating systems, some of which do not have / dev / random.

MySQL initializes the initial default value at server startup using the integer returned by time(0) . If you are interested in the source string, this is in the MySQL source in the sql / mysqld.cc file, function init_server_components() . I do not think he ever seeds again.

Then the subsequent "random" numbers are based solely on the seed. See source file mysys_ssl / my_rnd.cc, function my_rnd() .

The best solution for your random selection task, both for performance and for the quality of randomization, is to generate a random value between the minimum primary key value and the maximum primary key value. Then use this random value to select the primary key in the table:

 SELECT ... FROM MyTable WHERE id > $random LIMIT 1

The reason you will use> instead of = is that you may have spaces in the identifier due to deleting or rolling back lines, or you may have other conditions in the WHERE clause so that you have spaces between lines that match your conditions.

The disadvantages of this larger method are:

  • Lines following such a gap have a higher chance of choice, and the larger the gap, the higher the probability.
  • Before creating a random value, you need to know MIN (id) and MAX (id).
  • Doesn't work if you need some random strings.

Advantages of this method:

  • This is much faster than ORDER BY RAND (), even for a small table size.
  • You can use a random function outside of SQL.
+5
source

RAND is pseudo-random. Be careful using it for safety. I don’t think your “random selection of one line of fifty” is for security, so you are probably fine.

This is pretty fast for a small table. It will be terrible to select a random row from a large table: it will have to mark each row with a pseudo-random number and then sort them. For the application you are describing, @TheEwook's suggestion is perfectly true; sorting even a small table more than once a millisecond can flood even powerful MySQL hardware.

Do not put RAND ever if you are not testing and you need a repeating sequence of random numbers for some kind of unit test. I learned this hard way when I created what I thought were hard-to-define session tokens. The MySQL guys did a good job with RAND, and you can trust them for the application you're talking about.

I think (not sure) if you do not sow it, it starts with a random seed from / dev / random.

If you need crypto-sized random numbers, read / dev / random yourself. But keep in mind that / dev / random can only generate limited speed. / dev / urandom uses / dev / random to generate faster speeds, but not so high-grade in its entropy pool.

+1
source

If your table is not too large (for example, a maximum of 1000 records), this does not matter much. But for large tables, you should choose an alternative way.

This article can help you:

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

0
source

More articles:


All Articles