How to get random record from database?

Hello, please tell me as fast as possible to pull from the database record? I wrote this code

select `id`,`title` from `class_ads` where `active`='1' ORDER BY RAND() limit 1


but this query takes more than half a second, because the records in the database is a lot like to speed this up? the option of obtaining the total number of entries and receipt between it and 1 is any number , and then stand for this number in the where clause isn't working for me..
April 7th 20 at 10:50
3 answers
April 7th 20 at 10:52
as an option:

1) remove the count(id) the entire table is recorded temporarily in the cache.
2) use rand(1, $count) to get the unique records
3) get primary key of your record.
@Juvenal.Mohr , just add in the question and you answered, I is not suitable, because when counting the total number of entries would not count line is active, the result can be substituted by the line number where active=0, as well as when generating random numbers - Arnulfo commented on April 7th 20 at 10:55
How about you get in the id of the deleted record? Well, as an option? - Candace29 commented on April 7th 20 at 10:58
@Candace29, logically, Yes. - Juvenal.Mohr commented on April 7th 20 at 11:01
@Laurence, how many records? perhaps it would be logical to load everything into the memory array by ID.
Further taking into account caching, pull rand() ? - Juvenal.Mohr commented on April 7th 20 at 11:04
@Juvenal.Mohr, 200 thousand records.. - Arnulfo commented on April 7th 20 at 11:07
@Arnulfo, pseudo random on the new record? - Juvenal.Mohr commented on April 7th 20 at 11:10
@Arnulfo,
I is not suitable, because when counting the total number of entries will not be considered line active


So add a condition where id>=rand(1, $count) and active=1 - arno commented on April 7th 20 at 11:13
@Arnulfo, on habré the topic Sample random records in MySQL find out why a query with ORDER BY RAND() runs long and how it can be accelerated by 12 times.

View your query using EXPLAIN:
explain select `id`,`title` from `class_ads` where `active`='1' ORDER BY RAND() limit 1

he probably goes through the creation of a temporary table, full copy the source table and sorting: Using where; Using temporary; Using filesort, so it's unacceptably long on large tables.

PS: If the above solution is not acceptable, the view of the decision of habrovky:
Quick selection of random values from large tables...
MySQL. A selection of random rows in one query - lulu commented on April 7th 20 at 11:16
April 7th 20 at 10:54
April 7th 20 at 10:56
SELECT value
FROM table1 AS r1 JOIN
(
SELECT (RAND() * (SELECT MAX(id) FROM table1)) AS id
)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

Find more questions by tags MySQL