MySQL problem

[STTBoss]

Hello

I have a question about MySQL. I have a column probability_index in my table and need to select a random row, but the higher the probability_index is, the higher chance is for the row to be selected. Any ideas?

Thank you
STTBoss

[fatdicksimon]

you should be able to do

ORDER BY RAND(), probability_index DESC LIMIT 1

[STTBoss]

Originally Posted by fatdicksimon

you should be able to do

ORDER BY RAND(), probability_index DESC LIMIT 1

This doesn't solve it, this just sorts it by the random number and doesn't take the probability_index into account (only if the RAND() number is generated absolutely the same for 2 fields, which doesnt happen really often)

[fatdicksimon]

you could generate a random number then do an if clause inside of the query and say if random number >= something then select a random result only within a certain range of probability_index values (the high end of these values) otherwise, you would select a random result without taking into account the probability_index value.

so... you generate a random number 0-9, and if that random number>1, which will happen 80% of the time, you would only select a random value from the top end of probability_index values. this would cause higher probability_index values to show up more often.

[STTBoss]

Originally Posted by fatdicksimon

you could generate a random number then do an if clause inside of the query and say if random number >= something then select a random result only within a certain range of probability_index values (the high end of these values) otherwise, you would select a random result without taking into account the probability_index value.

so... you generate a random number 0-9, and if that random number>1, which will happen 80% of the time, you would only select a random value from the top end of probability_index values. this would cause higher probability_index values to show up more often.

yes, they would show up more often, but not at the rate of probability_index... anyways, thanks for your time, I think I figured something out and will post it in a while if it works

[Brujah]

may need to use some code for it, like php or other
without knowing the values of probability index column, the first that comes to mind that it might get done is:

create a temporary table
insert into the temp table an id for each probability index difference
- if it's 83, put it in 83 times
- next one is 41, insert the id 41 times

select id from temp table order by rand();
select * from main table where temp table id = main table id;

maybe not elegant, but it's the first thing that came to mind without knowing if a mysql function exists to handle it easier

[STTBoss]

Originally Posted by Brujah

may need to use some code for it, like php or other
without knowing the values of probability index column, the first that comes to mind that it might get done is:

create a temporary table
insert into the temp table an id for each probability index difference
- if it's 83, put it in 83 times
- next one is 41, insert the id 41 times

select id from temp table order by rand();
select * from main table where temp table id = main table id;

maybe not elegant, but it's the first thing that came to mind without knowing if a mysql function exists to handle it easier

great idea doing it with temporary table... i will try to form your idea into a more elegant solution, but thanks a lot!

[mrkris]

Originally Posted by STTBoss

great idea doing it with temporary table... i will try to form your idea into a more elegant solution, but thanks a lot!

temporary tables tend to slow down your queries.

[studiocritic]

Originally Posted by mrkris

temporary tables tend to slow down your queries.

depending on the frequency of inserts to this original table, he could just use a real table, but with Brujah's structure..

and fill the proper rows in this new table when inserts to original are done.

look up the mysql bulk insert syntax, and do this in php

[Brujah]

Originally Posted by mrkris

temporary tables tend to slow down your queries.

Not always. They obviously have some important uses. We don't really know a lot about his current design, or how he will use this.

[STTBoss]

OK guys this was a real pain in the ass, but here is the solution (works pretty smoothly, I tested it ;)) The way I use it is a more complicated, I adjusted the code for posting to this thread.

First, I added two INT columns to the table - from_no and to_no

mysql_query("ALTER TABLE table_name ADD COLUMN (from_no INT, to_no INT)") or die(mysql_error());

Then, I created a file that I will set up a cronjob for like every 20 minutes. This file will fill the "from_no" and "to_no" columns with number ranges. The first range starts with 0, no range overlaps, no gaps between the ranges. The larger the range, the better chance for the row to be selected.

$from_no = '0';
$result = mysql_query("SELECT id,probability_index FROM table_name") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    mysql_query("UPDATE table_name SET from_no='$from_no', to_no='" . ($from_no + $row[probability_index]) . "' WHERE id=$row[id]") or die(mysql_error());
    $from_no += $row[probability_index] + 1;
}

And finally, this code will generate random number within the range of 0 and the largest number in the to_no column and selects the right row

$row = mysql_fetch_array(mysql_query("SELECT MAX(to_no) * RAND() AS rnd FROM table_name"));
$rnd = $row[rnd]; 

$row = mysql_fetch_array(mysql_query("SELECT id FROM table_name WHERE from_no<=$rnd AND to_no>=$rnd LIMIT 1")) or die(mysql_error());

Looks pretty simple now, but I worked 2 hours on it. Hopefully don't I find out there's a function for it in MySQL ;)