Re: Get a Random Row on a HUGE db - Gary Richardson - com.mysql.lists.mysql

12 messages in com.mysql.lists.mysqlRe: Get a Random Row on a HUGE db

From	Sent On	Attachments
gunm...@gunmuse.com	26 Apr 2005 08:32
Jay Blanchard	26 Apr 2005 08:48
Rhino	26 Apr 2005 08:53
gunm...@gunmuse.com	26 Apr 2005 12:02
Peter Brawley	26 Apr 2005 12:20
gunm...@gunmuse.com	26 Apr 2005 12:28
gunm...@gunmuse.com	26 Apr 2005 12:30
Gary Richardson	26 Apr 2005 15:00
Christian Meisinger	27 Apr 2005 00:13
Dawid Kuroczko	27 Apr 2005 06:43
Scott Gifford	27 Apr 2005 08:33
Jigal van Hemert	28 Apr 2005 00:26

Subject:	Re: Get a Random Row on a HUGE db
From:	Gary Richardson (gary...@gmail.com)
Date:	04/26/2005 03:00:01 PM
List:	com.mysql.lists.mysql

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, gunm...@gunmuse.com <gunm...@gunmuse.com> 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.

-----

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