4

I have a table with more than 800K rows. I'm trying to get random 4 ID's. My query works fast, but it sometimes gives me one, sometimes two and sometimes no results given. Any idea why?

Here is the query:

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

the type='5' only has 1603 rows and it doesn't always give me 4 rows. when i change it to type='11' it works fine. 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 big and growing table, will need a quick query

user2636556
  • 1,905
  • 4
  • 32
  • 61

2 Answers2

1

Obviously. There are not necessarily any rows that meet the where condition.

One alternative is to dispense with the where clause entirely:

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

Here is a way to make this more efficient (and index on table(type) helps):

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;

The "5" is arbitrary. But it should generally fetch at least four rows.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

The rand function is reiterated for every line, so you get a Poisson-distribution number of matches. Could be 0, could be 1, could 312 - with different probabilities.

Aganju
  • 6,295
  • 1
  • 12
  • 23
  • Can you document the "Poisson-distribution" nature of `rand()`. I'm pretty sure it is a uniform distribution, and the two are very different. – Gordon Linoff Jul 28 '16 at 11:45