Re: Get a Random Row on a HUGE db
From: "Scott Gifford" >SELECT COUNT(*) FROM firebase_content; > > to get the count. That's very fast; it comes from the table summary > information, IIRC. I use a similar solution for a similar problem, > and have had great luck with it. This is true for MyISAM tables, but e.g. InnoDB does *not* store the total number of records per table seperately. A SELECT COUNT(*) on InnoDB will result in a real row count... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
<[EMAIL PROTECTED]> writes: [...] > 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; LAST_INSERT_ID() only works if you just inserted an element; it's maintained per-connection. See: http://dev.mysql.com/doc/mysql/en/getting-unique-id.html If your items are numbered sequentially, try just using: SELECT COUNT(*) FROM firebase_content; to get the count. That's very fast; it comes from the table summary information, IIRC. I use a similar solution for a similar problem, and have had great luck with it. To deal better with deleted items, you could periodically renumber your articles to avoid gaps in numbering. It would be great if MySQL had an optimization for this case. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
On 4/26/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > 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. > > $stmt = "Select * from firebase_content Order By rand() DESC Limit 0, 1"; In general what such a statement does is [ except optimizations ] 1) retrieve all rand() values for all rows 2) sort them according to rand value 3) return the one with highest/lowest value. To improve the speed you could add a WHERE clause that limits the number of rows to, for example 1% of the table: SELECT * FROM firebase_content WHERE rand() < 0.01 ORDER BY rand() LIMIT 1; ...so instead of returning and sorting 12000 rows you are doing it with about 120 rows which should be way faster. This is a trick I've learned while working with PostgreSQL -- and as I see it is universally useful. :) Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
> $last_row ="SELECT from firebase_content LAST_INSERT_ID()"; > $last_row_query = $dbi->query($last_row); > $last_row_result = $row->id; i think LAST_INSERT_ID will not work for what you wonna do. if you open a connection to MySQL and call LAST_INSERT_ID without a INSERT it will return 0. http://dev.mysql.com/doc/mysql/en/getting-unique-id.html " For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. " so it would work if you have only ONE connection for all INSERTs and your SELECT. i think you have to get the highest id first and then select a random row. instead of SELECT MAX() try SELECT `id` FROM `whatever` ORDER BY `id` DESC LIMIT 1; i tried this and it's faster then MAX(). ORDER BY RAND() seems to be the slowest method. chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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.
>
>
> -
>
> [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.
> >
> > >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]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get a Random Row on a HUGE db
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.
-
[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.
>
>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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get a Random Row on a HUGE db
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
-
[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.
>
>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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
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.
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]
RE: Get a Random Row on a HUGE db
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.
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
How about using the rand() function built into
MySQL? You could use it to generate a random number, then find the row whose
primary key equals that random number, then do a single-row select on that
row.
Rhino
- Original Message -
From:
[EMAIL PROTECTED]
To: Mysql
Sent: Tuesday, April 26, 2005 11:33
AM
Subject: Get a Random Row on a HUGE
db
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.
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() DESC
Limit 0, 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 = ""> $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;}*/?>
ThanksDonny LairsonPresident29
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
No virus found in this incoming message.Checked by AVG
Anti-Virus.Version: 7.0.308 / Virus Database: 266.10.2 - Release Date:
21/04/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get a Random Row on a HUGE db
[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.
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]
Get a Random Row on a HUGE db
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.
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() DESC
Limit 0, 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 = ""> $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;}*/?>
ThanksDonny LairsonPresident29
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
