need a alternative to ORDER BY RAND()

[brentmoeller]

i came across a link talking about how bad

ORDER BY RAND()

is.

 

Since

$result = mysql_query("SELECT * FROM slinks where approval='1' ORDER BY RAND() LIMIT 1") or
    die(mysql_error());

 

One of the main lines of code at my site is the above, so i decided to do a little research to see why this was bad. After a little reading i found out that its ridiculous on the server when you have a large database almost taking 132s to select a random results from 5000 rows.

My site has a feature that allows users to submit links to the database and as it gets popular im sure several thousands of links will be stored resulting in a MAJOR issue. So before it even gets to this point i would like to nip it in the butt and just use another method if possible.

[cunoodle2]

I'm not sure by try running some tests on this..

 

SELECT * FROM slinks where approval='1' AND id IN (select t.id FROM slinks t ORDER BY RAND() LIMIT 5) 

 

My code assumes you have an indexed variable called "id" and that is your key.  Also I would avoid using "SELECT *".. ideally only select what you need instead of all columns.

[TOA]

Read the Sticky: http://www.phpfreaks.com/forums/index.php?topic=125105.msg522853#msg522853

[brentmoeller]

thanks both of you

My new question is how can i add

where approval='1'

into this query

 

$result = mysql_query("SELECT * FROM slinks WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM slinks ) ) ORDER BY id ASC LIMIT 1")or die(mysql_error());

[TOA]

Anywhere in the WHERE clause

[brentmoeller]

i did it like this

$result = mysql_query("SELECT * FROM slinks WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM slinks ) )AND approval='1' ORDER BY id ASC LIMIT 1")or die(mysql_error());

 

It seems to be working does this look correct?

[TOA]

Without testing it, yeah it looks fine

 

Don't use * though...it's good practice to name your fields implicitly

 

And define your sql in another variable, it's cleaner and makes for easier testing

 

like this:

$sql = "Your sql";
$res = mysql_query($sql) or die(mysql_error());

[brentmoeller]

thanks dev

I have another related question. 

$result = mysql_query("SELECT * FROM slinks where approval='1' ORDER BY RAND() LIMIT 1") or
    die(mysql_error());

or

$result = mysql_query("SELECT * FROM slinks WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM slinks ) )AND approval='1' ORDER BY id ASC LIMIT 1")or die(mysql_error());

 

The code dont reproduce that good of random results. Both lines of code  produce the same random results often. The code selects the same urls back to back often then it may select a different url and then go back to the same url it already randomly picked 2 times in a role. So i guess the question is how can i keep the query from selecting the same urls over and over and back to back?

 

[brentmoeller]

Ok i have figured out what code i want to run and its

$result = mysql_query("SELECT count(*) FROM slinks where approval='1'");  
$do = mysql_fetch_row($result);  
$random = mt_rand(0,$do[0] - 1);  
$result = mysql_query("SELECT * FROM slinks LIMIT $random, 1");  

 

now all i need to figure out with a little help from the great users at phpfreaks is how i can get the random site to only be ran one time per user.

 

example

click = randomsite1.com

click = randomsite2.com

click = randomsite3.com

click = randomsite4.com

click = randomsite5.com

click = randomsite5.com = <--SKIP done been called so lets move along

click = randomsite6.com

 

 

I guess somehow im going to have to store the urls that are loaded into a variable and compare the variable to the the random site to make sure we dont get a match.

then again i guess i would have to some how figure out how to store the data per unique user as well .

 

Any suggestions would be greatly appreciated

[brentmoeller]

Im going to go ahead and post the latest post in a new thread since the main question in this thread has been resolved

[TOA]

I guess somehow im going to have to store the urls that are loaded into a variable and compare the variable to the the random site to make sure we dont get a match.

 

That's a reasonable start, and I've done it like that, but follow the link in the sticky. There's an article that talks about fixing holes and whatnot...might be some usefull info that you can use for your purpose.

 

then again i guess i would have to some how figure out how to store the data per unique user as well .

 

Not sure what you mean

 

Luck