Random selection from a MySQL db

[arclyte]

Hi,

I've been banging my head against the wall on this one, so hopefully someone has a better idea of what to do here than I...

I'm trying to make a homepage that randomly selects 6 items from our product database to display. Now, before you go saying that this is easy, there are a few caveats. I don't want it to be _completely_ random, and I want it to be quick.

The original code for the site was random, but then we'd randomly get 3 or 4 items from the same category showing up, but we want each item to be from a different category to show a wider spread of the products we have. So I rewrote the code in order to make it less/more random But that code is markedly slower than the original. I know why, I just don't know a better alternative... Here's some code...

Here is my current SELECT statement (shortened for sanity):

SELECT product.*, 
FROM product
WHERE category_key NOT IN ({$cat_exclude})
AND active = 1  
ORDER BY rand()
LIMIT 1

The ORDER BY rand() is sorting through some 25,000 items in the table every time it is run, so there is a noticeable delay. I've played around with a number of randomization techniques in PHP but haven't found a good solution yet. My problem is, I can break it into a few different queries, but I don't know that that will run any quicker. I'm seeking a simple and elegant solution but can't come up with one!

Here's my current code in psuedo-code:

while $i < 6 {
$cat_exclude = "0";
run query;
print result;
$cat_exclude = $cat_exclude . result[category_key];
$i++;
}

I tried a random process where I grab all the product_id's in question (which runs quickly) put them into an array, have php randomly select one, then run another query to get the info for that product_id. This runs fast, but it doesn't give me the ability to change the category each time. I'd have to re-run the initial query with the new category exclusions... I think this is going to slow it down considerably and not be any more optimized than what I have now.

Any ideas? Hopefully I've explained myself clearly enough. Let me know if you need more information. TIA.

Jim

 

[spookie]

I tried a random process where I grab all the product_id's in question (which runs quickly) put them into an array, have php randomly select one, then run another query to get the info for that product_id. This runs fast, but it doesn't give me the ability to change the category each time

Have you considered making seperate arrays for category and productids?

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.