need a alternative to ORDER BY RAND()

[coolguy]

i came across a link talking about how bad

Code: Select all

ORDER BY RAND()

is.

Code: Select all

$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.

[SamEA]

Well if we're talking about a link protector script that uses MySQL to import the links submitted by users, we are releasing a flat file database version very soon.

In response to your question, I advise you to read this: http://www.electrictoolbox.com/msyql-alternative-order-by-rand/.

[coolguy]

yeah that looks like the new method im using

Code: Select all

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

yeah this code is on a personal script in coding i have another question you may be able shed a light on..

the code im running is

Code: Select all

$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"); 


The above is finished off into a iframe

Code: Select all

<iframe src="<?php while ($row = mysql_fetch_array($result)){ print $row["url"];}?> "frameborder="0" width="100%" height="100%" name="surfing"id="dframe"></iframe>

resulting in a random site loaded ever time the php file is ran
now all i need to figure out with a little help 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

[SamEA]

Okay, before I go over anything have I understood your query correct?

This is how your script currently runs:

// user-side (uploading)

1) User submits URL
2) PHP handles the submitted URL and stores it into a MySQL database.

// user-side viewing

1) User clicks on most popular Top 10 links.
2) PHP fetching a random top 10 link from the MySQL database.
3) If a user clicks on the link, it prevents it from further showing to other users?

A demo of this will be easier to understand your query. Are you hosting this on your localhost? If so, please upload and PM or mention a link in your next post.

[coolguy]

Yes im on a local host illo show you what i have
This is a project ive been working on for some time and has no relation to the upload script. What this part of the script does is calls a a random link that is in the database as long as its been approved and posts runs the link inside of a iframe
now what i need to figure out is how to make sure that the random url that's pulled out of the database isn't shown more than one time per user.

Code: Select all

<?php

/**
 * @author Brent Moeller
 * @copyright 2011
 */

include ('functions.php');
db_connect();
//////////////original method extremly server heavy when there are several hundred rows//////////////////
//$result = mysql_query("SELECT * FROM slinks where approval='1' ORDER BY RAND() LIMIT 1") or die(mysql_error());
////////////////////////////////////////////////////////////////////
/////////////another method //////////
//$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());
//////////////////////////////////////////////////////////////

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


?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="mouseovertabs.css" />
<style type="text/css">
A:visited {text-decoration: none; color: black;}
A:active {text-decoration: none}
A:link {text-decoration: none; color: white;}
</style>

<STYLE type="text/css">
body {overflow:hidden}
</STYLE>
<script src="mouseovertabs.js" type="text/javascript">

/***********************************************
* Mouseover Tabs Menu- (c) Dynamic Drive DHTML code library (www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at http://www.dynamicdrive.com/ for this script and 100s more
***********************************************/

</script>
<title>DizzyUrl Discovery Engine</title>
</head>
<body>
  <div id="mytabsmenu" class="tabsmenuclass">
<script type="text/javascript">
function closeFrame() {
    document.getElementById("dframe").style.display="none";
}
</script>

<ul>
<li><a href="explore.php">NEXT</a></li>
<li><a href="explore.php">+</a></li>
<li><a href="explore.php">-</a></li>
<li><a href="http://localhost/Stumble/explore.php" rel="gotsubmenu[selected]">ACCOUNT</a></li>
<li><a href="http://www.cssdrive.com" rel="gotsubmenu">OPTIONS</a></li>
<li><a href="javascript:closeFrame();">-X-</a></li>
</ul>
</div>

<div id="mysubmenuarea" class="tabsmenucontentclass">

<!--1st link within submenu container should point to the external submenu contents file-->
<a href="submenucontents.htm" style="visibility:hidden">Sub Menu contents</a>

</div>

<script type="text/javascript">
//mouseovertabsmenu.init("tabs_container_id", "submenu_container_id", "bool_hidecontentsmouseout")
mouseovertabsmenu.init("mytabsmenu", "mysubmenuarea", true)
</script>           
<iframe src="<?php while ($row = mysql_fetch_array($result)){ print $row["url"];}?> "frameborder="0" width="100%" height="100%" name="surfing"id="dframe"></iframe>
</body>

</html>



[SamEA]

E-mail [email removed] or attach the whole script including the MySQL database.