Select RANDOM row from 60k rows fast with conditions

Asked Mar 27 '17 at 06:56

Active Mar 27 '17 at 07:08

Viewed 39 times

I have mysql database 60k rows, 4 columns (ID,Keyword,Status,Updet ) and try to select rand fast using this :

SELECT Keyword 
FROM keyword AS r1 
JOIN (
    SELECT CEIL(RAND() *(SELECT MAX(id) FROM keyword)) AS id
) AS r2 
WHERE r1.id >= r2.id 
ORDER BY r1.id ASC LIMIT 1

but the problem is i can't select the row with conditions Updet='published', and have try

SELECT Keyword 
FROM keyword AS r1 
JOIN (
    SELECT CEIL(RAND() *(SELECT MAX(id) FROM keyword)
) AS id) AS r2 ON r1.id >= r2.id 
WHERE Updet='published' 
ORDER BY r1.id ASC LIMIT 1

not working, please help..

edited Mar 27 '17 at 07:08

Your Common Sense

156,878
40
214
345

asked Mar 27 '17 at 06:56

sugi putra

I'm not an expert on MySql, but can't you just use `ORDER BY RAND()`? – Zohar Peled Mar 27 '17 at 07:03
the result will slow respond and overhead for sorting each row – sugi putra Mar 27 '17 at 07:07
i can't understand it, can you explain it to me? – sugi putra Mar 27 '17 at 08:55

Select RANDOM row from 60k rows fast with conditions

0 Answers0