List:General Discussion« Previous MessageNext Message »
From:Gary Richardson Date:April 26 2005 10:00pm
Subject:Re: Get a Random Row on a HUGE db
View as plain text  
Why don't you generate a random integer in your code and select for an
article? If there is no article there, do it again. Even if you have
to call it 50 times it may be faster than doing a full scan on the
table.

It may not work so well if there are lots of gaps in your autoincrement.

In perl (don't know about PHP), you could pass your MAX(article_id) to
RAND to limit the outside of the random number generated. You may need
to call int() on it though as it may be a float.

On 4/26/05, gunmuse@stripped <gunmuse@stripped> wrote:
> 
> This difference between using a 40 mb table and 4mb table with the same
> traffic was a 70 server load versus a .9 server load.  So it was the amount
> of data that I was selecting that was choking this feature.
> 
> 
> -----
> 
> gunmuse@stripped wrote:
> 
> >Thanks for that I implemented to my Random code.  Same problem that select
> *
> >portion is just a nightmare.  Remember I selecting 38mb of data when I do
> >that.
> >
> >What I want to do is jump to a Valid random row.  Now If I didn't delete
> >content often that would be easy grab the last autoincremented row_id and
> >get a random number between 1 and End  Jump to that row to create the link.
> >Very fast. Zero load
> >
> >So what I am trying is this.
> >
> >$last_row ="SELECT from firebase_content LAST_INSERT_ID()";
> >$last_row_query = $dbi->query($last_row);
> >$last_row_result = $row->id;
> >
> >But what I am seeing is this:
> >
> >Object id #9
> >
> >and not the number that is in the database.
> >
> >What am I sending to this variable that is wrong?
> >
> >
> >
> >[snip]
> >I am wanting to display a random page from my site, But I have over
> >12,000 articles right now and we add over 150 per day.  What I wound up
> >doing was a Virtual DOS attack on my own server because the 40 mb db was
> >being loaded to many times.
> >
> >I have tons of memory and a Dell Dual Xeon 2.8 gig.
> >
> >Can someone think up a better way of doing this?  I wish Mysql would
> >just bring me back 1 valid random row  It could be used in so many ways
> >it should just be a part of MySql anyway.
> >
> ><?php
> >ini_set("display_errors", '1');
> >header("Pragma: private");
> >header("Cache-Control: post-check=0, pre-check=0", false);
> >header("Cache-Control: no-cache, must-revalidate");
> >require_once("firebase.conf.php");
> >$dbi = new DBI(DB_URL);
> >$stmt = "Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1";
> >$result = $dbi->query($stmt);
> >while($row = $result->fetchRow())
> >{
> > $title = $row->title;
> > $cate = $row->category;
> > $get = "Select cat_url from firebase_categories where
> >cat_name='$cate'";
> > $now = $dbi->query($get);
> > $rows = $now->fetchRow();
> > $url = $rows->cat_url;
> > $link = $url . $title;
> >}
> >header("Location: http://www.prnewsnow.com/$link");
> >exit;
> >/* Sudo code that I am trying to create to relieve server stress.
> >function randomRow(table, column) {
> >var maxRow = query("SELECT MAX($column) AS maxID FROM $table");
> >var randomID;
> >var randomRow;
> >do {
> >randomID = randRange(1, maxRow.maxID);
> >randomRow = query("SELECT * FROM $table WHERE $column = $randomID");
> >} while (randomRow.recordCount == 0); return randomRow;
> >}
> >*/
> >?>
> >[/snip]
> >
> >Try this ...
> >SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;
> >
> >12000 rows is not huge at all, so this should be pretty quick
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
> >
> >
> >
> >
> 
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
>
Thread
Get a Random Row on a HUGE dbgunmuse26 Apr
  • Re: Get a Random Row on a HUGE dbRhino26 Apr
  • Re: Get a Random Row on a HUGE dbDawid Kuroczko27 Apr
RE: Get a Random Row on a HUGE dbJay Blanchard26 Apr
  • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
    • Re: Get a Random Row on a HUGE dbPeter Brawley26 Apr
      • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
      • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
        • Re: Get a Random Row on a HUGE dbGary Richardson27 Apr
    • Re: Get a Random Row on a HUGE dbChristian Meisinger27 Apr
    • Re: Get a Random Row on a HUGE dbScott Gifford27 Apr
    • Re: Get a Random Row on a HUGE dbJigal van Hemert28 Apr