Removing RAND()

[dreamwest]

I had to remove rand from my queries because my database is over 3 million rows and it was serioulsly affecting the server

 

Is it possible to compress this alternate rand query into one line aka one mysql_query():

 

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `video` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result3 = mysql_query( " SELECT * FROM `video` WHERE approve='1' and `show`='1'  LIMIT $offset, 10 " );

[sader]

hmm maybe let php do random stuff?

$offset_result = mysql_query("SELECT id  FROM `video`");
$offset = floor((rand() / getrandmax()) * mysql_num_rows($offset_result));

[sader]

I just understand now one thing u should substract from rows count the number of items u want to display like so:

$offset_result = mysql_query("SELECT id  FROM `video`");

$offset = floor((rand() / getrandmax()) * (mysql_num_rows($offset_result)-10)); //just to be sure that u will always be able to retrive 10 videos from offset

[dreamwest]

Thanks.

 

Problem is it doesnt reduce the amount of code for the query/ies and ill still have 3 queries. I was trying to reduce it to one combined query thus creating less code, easier to remember & less cluttered apps - it may be possible with subqueries, ill have to brush up on that

 

 

[fenway]

Unfortunately, you still can't use an expression in the limit clause...