How to select a random record from MySQL database without using primary key and order by rand()

Question

MySQL

There was such a problem, there is a database that contains users (users who installed the VKontakte application, to be precise), as the Primary key, the user id in the social network is used, which can be considered a random number. You need to select one random user from the database. There are a lot of users in the database, so order by rand() is too expensive to use, generating a random number and choosing a record with the same id will also fail, given that the ids are out of order. How to be in such a situation? And at the same time, what if you need several random users?

Answer 1

The first thing that comes to mind is to play around with limit, for example limit <random number>,<sample size>

Answer 2

The best solution is to take a random in the range in which the primary key lies - and get a record whose key is greater than or equal to this random. Like this:

SELECT * FROM my_table
WHERE pk_column >=
(SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table)
ORDER BY pk_column
LIMIT 1;

Of course, problems arise in tables where large ranges of records are deleted, but there are also solutions for this, such as introducing an additional end-to-end field without “holes”. You can read more details here

Answer 3

hudson.su/2010/09/16/mysql-optimizaciya-order-by-rand/
habrahabr.ru/blogs/mysql/55864/
habrahabr.ru/blogs/mysql/54176/

Answer 4

add a table like

Answer 5

Denormalize id to SET in Redis and use SRANDMEMBER.

Answer 6

get a uRAND field in the table and once a day sit there the true RAND
after which SELECT * FROM table WHERE uRAND<somerandvalue ORDER BY uRAND DESC LIMIT 1