List:General Discussion« Previous MessageNext Message »
From:Dawid Kuroczko Date:April 27 2005 1:43pm
Subject:Re: Get a Random Row on a HUGE db
View as plain text  
On 4/26/05, gunmuse@stripped <gunmuse@stripped> 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
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