Need help with random mysql query

I am having an incredibly difficult time trying to re - work a mysql query so that it pulls a random row id from a table and also matches a specific category name.

In other words, I have a sql statement as follows:

$result = mysql_query( " SELECT * FROM 'table' WHERE category = theme[cat] LIMIT 0, 5;

PHP:

This is a query from the script running on one of my sites that should display 4 boxes in each category. What I want to do is have those boxes display randomly. Now I know I could use order by rand() and call it a day but that makes for horrific nightmare performance wise as I am working with > ~3k rows.

:-(

 

its simple..use the rand() function..

SELECT * FROM 'table' 
WHERE category = theme[cat] 
ORDER BY RAND()
LIMIT 0, 5

Code (markup):

hope this helps

 

Thanks for that olddocks,

The problem with order by rand is that it is terrible on performance.

I cannot simply select a random id from the table either because I need to select a random id FROM the category that I have. i.e. I have five categories = (stuff1, stuff2, stuff3, stuff4, stuff5).

Each category has 1000 'things' in it. I need to randomize the 1000 things AFTER I have pulled a specific category.

:-(

 

I think that is your best shot. It's the only way I know of doing it.

 

Thanks.. I guess for now I will stick with it.

I assume since it is only pulling specific categories that the query is only ordering by about 250 - 500 rows for any one query but I saw some slight performance decrease in my site loading time DIRECTLY after I set the script up this way.

Perhaps it was pure coincidence but who knows.

 

may be you should do this way. change the table structure and club all your records in one table. Then apply for random. I dont think performance will be a problem if you limit to 5 records

Table 1: (records)
ID | Name | Category ID

Table2: (categories)
ID | Category Name |

Perhaps you should give a try

 

Thanks again Olddocks. This is actually how the server is setup now with mysql. However I think there may be a better solution that I'm missing. Order by Rand by nature causes mysql to slow if you think about it. What it's doing is creating a dataset then sorting that dataset. It has always been more performance friendly to perform a random sort on a data size that is the same size as the amount of rows found in the database that your wanting to pull random numbers from. In my case, EACH category will still have in excess of 500 rows.

I'll have to keep my eyes peeled for something a little different but it works for now...

 

Try adding an artificial numeric key to the table, something like row_id, or possibly row_id_in_cat. Then using rand() function (and round() to go with it) generate a random key in the range so you'll always have a hit. Or possibly generate a narrow range where you will always have a hit. Generate 5 random numbers or ranges.

 

Ok i got a simple random problem... What im trying to do is grab the newest 15 links which I have a TIME table for, then out of those new 15 i want to choose 1 random link. The code below works, but it grabs 15 randoms out of the entire 200 link list then chooses one.

$result=mysql_query("select * from babeloggerv3 where approved = 'yes'  AND '".time()."'>=time ORDER BY RAND() LIMIT 1, 15"); 
while($row=mysql_fetch_array($result)){header("Location: http://www..com/plugs/out.php?id=$row[id]");}

Code (markup):

-thanks in advance

-tommy