How to efficiently get a random set of records from a DB

[Laphan]

Hi All

I use a MySQL DB with my ASP classic web app.

I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site. Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.

I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.

My initial thought was bring back all of the records to a local array (like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.

The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a sequential
number - its alphanum.

Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?

Thanks

[Bob Barrows [MVP]]

Re: How to efficiently get a random set of records from a DB

Laphan wrote:

Hi All
>
I use a MySQL DB with my ASP classic web app.
>
I've been asked if I can create a routine whereby I get a random
number of products (records) from the DB and display these on the
site. Basically every time a visitor hits the home page, they want
the site to display a random selection of say 6 products.
>

I have nothing to add to this:

http://www.aspfaq.com/show.asp?id=2132

.... especially since I'm not familiar with the SQL dialect used by MySQL

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Captain Paralytic]

Re: How to efficiently get a random set of records from a DB

On 21 Sep, 11:48, "Laphan" <ple...@dont.sp am.comwrote:

Hi All
>
I use a MySQL DB with my ASP classic web app.
>
I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site.  Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.
>
I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.
>
My initial thought was bring back all of the records to a local array (like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.
>
The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a sequential
number - its alphanum.
>
Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?
>
Thanks

Ask this over on comp.databases. mysql.
Even better, just search its archives, you will find the answer to
this question, there.

[Ted Dawson]

Re: How to efficiently get a random set of records from a DB


"Laphan" <please@dont.sp am.comwrote in message
news:eHH4Xe9GJH A.4956@TK2MSFTN GP06.phx.gbl...

Hi All
>
I use a MySQL DB with my ASP classic web app.
>
I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site. Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.
>
I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.
>
My initial thought was bring back all of the records to a local array
(like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.
>
The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a
sequential
number - its alphanum.
>
Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?
>
Thanks
>
>