Most efficient way of randomly selecting any mysql value given multiple values to match it to

Question

Ok, so I have a list of values, and I want it so the user can select any 7 different options, and it'll randomly select x number of values from the table with any of those options.

The stucture is like
ImgID (unique key) | SiteID | Other tables

Try 1: The first method I tried was read the database for any selected values, and count the amount for each selected option. The amount of occurrences were stored in one variable (just call it num), and the total amount was calculated too. It then chose a random number between 1 and the max amount, ran through the num variable with the individual amounts, incrementing it until it went past the value, revealing which option value the random value landed on. This could randomly choose the option correctly, but it'd still require another random selection from the database, which leads into the next try.

Try 2: The random value from the database seemed reasonable, but I thought why not just do it all at once and skip a lot of queries. I can't seem to get it working properly though, I was using this site - http://akinas.com/pages/en/blog/mysql_random_row/ and chose the bottom mehod to use. There's quite a few error messages from this - 'unable to save result set', 'execution was interrupted', 'lost connection to mysql server' and 'not a valid result resource'

The code is

for($i=0;$i<count($sites)-1;$i++){
    $siteinfo = explode("@",$sites[$i]);
    $siteid = $siteinfo[0];
    $sitegroup = $siteinfo[1];
    if($i!=0){
        $sqlextra .= " OR ";
    }
    $sqlextra .= "SiteID='".$siteid."'";
}
$sql="SELECT * FROM ImageList WHERE ImgID >= (SELECT FLOOR( MAX(ImgID) * RAND()) FROM 
ImageList) AND Valid='1' AND (".$sqlextra.") ORDER BY ImgID LIMIT 1";
if(mysql_query($sql, $mysql_connect)){}
else{echo mysql_error();}

And the sql output with a few of the option selected is

SELECT * FROM ImageList WHERE ImgID >= (SELECT FLOOR( MAX(ImgID) * RAND()) FROM ImageList) AND Valid='1' AND (SiteID='6' OR SiteID='7') ORDER BY ImgID LIMIT 1

I'm not very good at mysql so not sure if I've gone wrong somewhere, but if anyone has a better way of doing this or just knows why mine isn't working, it'd be appreciated, cheers

user3232725 · Accepted Answer · 2014-01-24 16:03:00Z

1

select * from ImageList where Valid='1' AND (SiteID='6' OR SiteID='7') order by RAND() Limit 1

This should be a lot more efficient :)

answered Jan 24, 2014 at 16:03

user3232725

2181 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CD001 Over a year ago

ORDER BY RAND() should never be considered efficient - it really isn't, read the article linked to in the OP for instance : akinas.com/pages/en/blog/mysql_random_row

Peter Over a year ago

Ah thanks man, works perfectly :) Edit: Oh, it seems to work in about 1 second, but there is 9000 values, so is that a bit slow or alright?

Collectives™ on Stack Overflow

Most efficient way of randomly selecting any mysql value given multiple values to match it to

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related