Selecting a random field in MySQL

Question

MySQL

Hello.

Until recently, I used the ORDER BY rand () crutch, but as the table grows, its speed drops.

It is required to display a random field. Records are fragmented (id is out of order and contains holes).

If it somehow simplifies the task, I use ZF.

Answer 1

Let A be the result of SELECT MAX(id) FROM `my_table` (it can be stored separately in key-value storage and sometimes updated)
Generate a number from 1 to A inclusive, let it be B on the client side (in this case, the php application ).
And select the first entry whose id >= B (SELECT `pole` FROM `my_table` WHERE `id` >= B LIMIT 1)
Try it. Should help.

Answer 2

There was already such a question:
habrahabr.ru/qa/5525/
And topics on this topic:
habrahabr.ru/blogs/mysql/54176/
habrahabr.ru/blogs/mysql/55864/
habrahabr.ru/blogs/mysql/104366/

Answer 3

1) Zabatsat table id<->realid. You can fill it very quickly with insert into select from. Accordingly, the id in it will not be fragmented and it will be very easy to select.
2) You can do the same as in item 1, making a table simply from realid, filling it in the same way, and randomly shuffle it. And choose a random number by positioning with the help of a limit, having previously generated a random number based on the number of records.

Answer 4

If you understand the task correctly, you need to get 1 random row from the table, where ids can be with holes.
Offhand LIMIT RAND($max),1

Answer 5

This will be fast and not afraid of inconsistent ID sequences
max = "SELECT COUNT(*) FROM `TABLE` (can be cached)
$random_start = mt_rand(0, max-1);
result = SELECT * FROM `TABLE` LIMIT "".$random_start.",1

Answer 6

Keep gaps