List:General Discussion« Previous MessageNext Message »
From:<gunmuse Date:April 26 2005 7:29pm
Subject:RE: Get a Random Row on a HUGE db
View as plain text  
What I had to do was do this for my navigation db and not my content db.  My
server can easily handle lots of calls to a 4mb table then tell it to fetch
the content once that has been achieved.

The reason I bringing this up is this seems to be a "patched" way of doing
this.

If I have 40,000 items in db that get updated and row_ids change for a
catalog and want to randomly display a product.

I should be able to ask Mysql for a Random valid row.  It indexes with a
Primary so it knows what valid at that time.  There is too much jumping
around just to say get random and be fair about it, so no one row comes up
every time or more often that others.


Gunmuse,

SELECT from firebase_content LAST_INSERT_ID()

In that cmd, 'from ...' ain't right.

I didn't understand either what's wrong with ORDER BY RAND() LIMIT 1.

Also check the Perl manual for how to retrieve a single value.

PB

-----

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



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