I've tried using "order by RAND(NOW()) limit 1", but it is ridiculously slow....it took about 6 seconds compared to 0 seconds without it.
mysql> select ROWID from REVIEWS
LEFT JOIN rm_votes on REVIEWS.ROWID=rm_votes.revid and rm_votes.revtype='product'
where rm_votes.ts is NULL and REVIEWS.status='unchecked'
order by RAND(NOW()) limit 1;
+-------+
| ROWID |
+-------+
| 76067 |
+-------+
1 row in set (6.05 sec)
mysql> select ROWID from REVIEWS
LEFT JOIN rm_votes on REVIEWS.ROWID=rm_votes.revid and rm_votes.revtype='product'
where rm_votes.ts is NULL and REVIEWS.status='unchecked'
limit 1;
+-------+
| ROWID |
+-------+
| 333 |
+-------+
1 row in set (0.00 sec)
Is there any faster way to grab a random row from a table with 50,000+ rows?
Thanks,
Andrew
