vb.org Archive - Need help optimizing a mySQL query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Need help optimizing a mySQL query (https://vborg.vbsupport.ru/showthread.php?t=318896)

JamesAB

05-31-2015 08:16 PM

Need help optimizing a mySQL query

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

Code:

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	06-01-2015 02:51 PM

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

06-02-2015 03:52 PM

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

All times are GMT. The time now is 02:09 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00954 seconds Memory Usage 1,713KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (3)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: