Need help optimizing a mySQL query

[JamesAB]

Here's the query I'm trying to optimize:

SELECT *
FROM vb3_jabvideo AS video
LEFT JOIN vb3_attachment AS attachment ON (video.attachmentid = attachment.attachmentid)
LEFT JOIN vb3_user AS user ON (attachment.userid = user.userid)
LEFT JOIN vb3_post AS post ON (post.postid = attachment.postid)
LEFT JOIN vb3_thread AS thread ON (post.threadid = thread.threadid)
WHERE video.isdeleted = 0 AND attachment.visible = 1 AND post.visible = 1 AND thread.visible = 1 
ORDER BY RAND() 
LIMIT 30

It's currently taking about 2 seconds. There are more than 60,000 rows in the vb3_jabvideo table.

I believe the heart of the problem is ORDER BY RAND()

Any mySQL experts out there that could offer advice for optimizing this query or another solution for selecting 30 random rows from this table?

Thanks for your help,
James

[kh99]

That's a good question to look up on stackoverflow.com. There's one answer here: http://stackoverflow.com/questions/1...-by-rand-query. Basically, do a query (if you need to) to get the number of rows in the table, then do 5 queries with a random OFFSET. But you have 30 to get, so I guess you'd need to test to see if doing 30 separate queries will be faster that the one you have. Edit: oh, well, your query has some things it checks in the "WHERE', so I guess you'd have to be prepared to do more than 30 queries so that if the conditions for a given row aren't true you can get another. So maybe that's not really a good solution for you.

Also, unless you really need each page request to get a different 30 random rows, you could cache the results and only do the query once a minute, for example.

[JamesAB]

Thanks for the advice. I've started doing some reading, but haven't tried coding anything for this yet. I also came accross this link:
http://jan.kneschke.de/projects/mysql/order-by-rand/

I'm not sure if using a stored procedure would be suitable for what I need or not.

Thanks,
James