LIMIT does not always return the same number of rows

Question

I have a table with more than 800K rows. I am trying to get random 4 IDs. My query is fast, but sometimes it gives me one, sometimes two, and sometimes no results. Any idea why?

Here is the request:

select * from table
  where (ID % 1000) = floor(rand() * 1000)
  AND `type`='5'
  order by rand()
  limit 4

type='5'only has lines 1603, and it doesn't always give me 4 lines. when I change it to type='11', it works great. Any idea how to fix this?

here is my code in Yii

$criteria = new CDbCriteria();
$criteria->addCondition('`t`.`id` % 1000 = floor(rand() * 1000)');
$criteria->compare('`t`.`type`', $this->type);
$criteria->order = 'rand()';
$criteria->limit = 4;

return ABC::model()->findAll($criteria);

PS: being a large and growing table, a quick query will be required

Answer 1

Obviously. There are no rows matching the condition where.

, where:

select t.*
from table t
where `type` = 5
order by rand()
limit 4;

( table(type) ):

select t.*
from table t cross join
     (select count(*) as cnt from table t where type = 5) x
where `type` = 5 and
      rand() <= 5*4 / cnt
order by rand()
limit 4;

"5" . , , .

Answer 2

rand , . 0, 1, 312 - .