Random row from MySql table, now half a second. How about as fast as possible?

Question

MySQL

The table has half a million records. You need to randomly select one to fill in the other, the test table.
Now it takes half a second to get a random string... This is very long.
I try like this:

$loc = DB::table("localities")
                ->select("id")
                ->orderBy(DB::raw('RAND()'))
                ->take(1)
                ->get();

This is equivalent to:

0 => array:3 [▼
    "query" => "select `id` from `localities` order by RAND() asc limit 1"
    "bindings" => []
    "time" => 95.69
  ]

Half a second:
And so half a second:
$loc = Locality::inRandomOrder()->first();
This is equivalent to:

0 => array:3 [▼
    "query" => "select * from `localities` order by RAND() limit 1"
    "bindings" => []
    "time" => 432.94
  ]

And this is all for a long time, indexes on table id are by default:

Неуникальный: 0
Имя ключа: PRIMARY
Порядок поля: 1
Имя колонки: id
Сравнение: A
Размер: 186060
Обработка: NULL
Сжатие: NULL

Answer 1

1. Determine the number of records in the table. Not the last ID, namely number
2. Determine a random value between 0 and number-1 records, this will be offset
3. Get the required record from the table:
I did not compare the speed, but it should be many times faster.
Random offset can be obtained both on the PHP side and on the MySQL side.
And you have already been written about RAND ().
You have N records in the table, you need to get through mt_rand(), for example, a random number from 0 to N-1. This will be your offset.

$count = 100;
$offset = mt_rand( 0, $count-1 );

Answer 2

RAND() is a heavy operation on its own and should not be used. There are many articles on the Internet (including Habré) devoted to ORDER BY RAND () optimization, for example, here is one of them

Answer 3

ORDER BY RAND() is not indexed in principle
You can try

SET @rand = (SELECT (MAX(`id`)-MIN(`id`))*RAND()+MIN(`id`) FROM `localities``);
SELECT * FROM `localities` WHERE WHERE `id` > @rand LIMIT 1;

But the distribution will be uneven, in places where there are gaps in `id`, the lines following them will be more likely to fall out.