faster/less intensive way of getting a random row from db?

Hello

I am noob about mysql so I am not sure if there exists a better
command/method to solve my problem. I read online about how using
rand() and the typical SELECT FROM table LIMIT rand(),1 is very slow
on a big table. The current method I was using is:

SELECT count(*) AS max FROM table
then use PHP code to generate a number from 0 to max, call it
$rand_row
then SELECT FROM table LIMIT $rand_row,1


This method has been working fine, until...sigh...i had to undergo
major site revision. and now I cannot just select from the table, I
have to check fields to only select the rows that contain certain
fields, so I do it like this:

SELECT count(*) AS max FROM table WHERE option1 = 'y'
then use PHP code to generate 0 to max, call it $rand_row
then SELECT * FROM table WHERE option1 = 'y', set the results to
$result
then random_id = mysql_result($result, $rand_row, 'id')

My question is, would this go crazy once my table is populated with
1000000 rows? I am doing a SELECT * but really only want to use 1 row.
Is there a better way?

SELECT * FROM my_table ORDER BY RAND() LIMIT 1; ?

On May 31, 3:10 pm, strawberry <zac.ca...@gmail.com> wrote:
> SELECT * FROM my_table ORDER BY RAND() LIMIT 1; ?

sir, u missed my points completely. and also I need to avoid rand()
function as it is documented to be very slow on big tables.

"flagman5" <flagman5@gmail.com> wrote in message
news:1180652488.053756.143240@j4g2000prf.googlegro ups.com...
> On May 31, 3:10 pm, strawberry <zac.ca...@gmail.com> wrote:
>> SELECT * FROM my_table ORDER BY RAND() LIMIT 1; ?
>
> sir, u missed my points completely. and also I need to avoid rand()
> function as it is documented to be very slow on big tables.
>

How the hell do you expect to get a random entry without using random
numbers?

Why not generate a list of random numbers before hand and use them if your
concerned with the speed of rand()?

flagman5@gmail.com wrote:
> Hello
>
> I am noob about mysql so I am not sure if there exists a better
> command/method to solve my problem. I read online about how using
> rand() and the typical SELECT FROM table LIMIT rand(),1 is very slow
> on a big table. The current method I was using is:
>
> SELECT count(*) AS max FROM table
> then use PHP code to generate a number from 0 to max, call it
> $rand_row
> then SELECT FROM table LIMIT $rand_row,1
>
>
> This method has been working fine, until...sigh...i had to undergo
> major site revision. and now I cannot just select from the table, I
> have to check fields to only select the rows that contain certain
> fields, so I do it like this:
>
> SELECT count(*) AS max FROM table WHERE option1 = 'y'
> then use PHP code to generate 0 to max, call it $rand_row
> then SELECT * FROM table WHERE option1 = 'y', set the results to
> $result
> then random_id = mysql_result($result, $rand_row, 'id')
>
> My question is, would this go crazy once my table is populated with
> 1000000 rows? I am doing a SELECT * but really only want to use 1 row.
> Is there a better way?
>
it depends! what database engine are you using? for myisam, this is not
gong to be a huge problem but for innodb, yes, it is! your biggest
problem is this code << select count(*) as max from table where
option1='y' >>
also, wouldn't use max as the column alias since it is a reserved word.

> On May 31, 3:10 pm, strawberry <zac.ca...@gmail.com> wrote:
>> SELECT * FROM my_table ORDER BY RAND() LIMIT 1; ?
>
> sir, u missed my points completely. and also I need to avoid rand()
> function as it is documented to be very slow on big tables.

The rand() function itself has nothing to do with tables, so it cannot
be "slow on large tables". What is slow is sorting an entire table by
random numbers and than just picking only one of them. Things would be
sped up tremendously if you could just use an indexed field for the
selection. One approach it to determine the maximum value of, for
instance, the primary key field. Then use the rand() function to
generate a ramdom number in the range covered by the existing key
values. If the generated key value does not exist, try again. If your
table does not have many "holes", this might be a fast option. You could
write a stored procedure to further minimize the queries.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/