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]
