Re: Get a Random Row on a HUGE db

[Peter Brawley]

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

-----

[EMAIL PROTECTED] 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/[EMAIL PROTECTED]

--
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/[EMAIL PROTECTED]