最快的方式从DB获取随机记录 [英] Fastest way get random record from DB

问题描述

我试过这个

  SELECT id 
 FROM users 
 JOIN（
 SELECT CEIL（RAND（）* ））AS id 
）AS r2 
 USING（id）
  

将工作，如果所有的id都增加，但是使用这个特定的表，他们不是。 id是相当随机的。

有人有更好的方法吗？谢谢！

解决方案

也许创建另一个表，并放在所有id的紧凑方式，添加id int autoincrement？如果你经常使用这个功能，新表将自己支付。

I've been using order by rand() and it's becoming too much of a bottle neck.

I've tried this

SELECT id
FROM users
JOIN (
  SELECT CEIL( RAND( ) * ( SELECT MAX( id ) FROM users ) ) AS id
) AS r2
  USING ( id )

And it would work if all the id's were incremented, however with this particular table, they're not. The id's are fairly random themselves.

Anyone have a better method? Thanks!

解决方案

Maybe create another table and put there all id's in compact way, adding id int autoincrement? If you using this function often, new table will pay for itself.

这篇关于最快的方式从DB获取随机记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！