azher
04-08-2004, 05:15 PM
Hey, all.
I'm trying to do a "Latest Images" type hack for my forums and while I've seen the other "Recent Attachments" hack that was posted for vB3, it doesn't account for forum permissions or deleted posts, so I thought I'd make my own.
I'm getting a bit hung up, however, on a slow query and I was wondering if anyone else may be able to help shed some light on the matter.
I know that MySQL JOINs can sometimes slow things down as is evident here:
SELECT post.userid, post.username, post.postid, deletionlog.primaryid, thread.title, thread.forumid, attachment.postid, attachment.filename, attachment.thumbnail_dateline
FROM post AS post, thread AS thread
LEFT JOIN attachment AS attachment ON ( attachment.postid = post.postid )
LEFT JOIN deletionlog AS deletionlog ON ( deletionlog.primaryid = post.postid )
WHERE post.attach > 0 AND attachment.thumbnail_dateline > 0 AND thread.forumid NOT
IN (
".implode(',', $limitfids)."
) AND ISNULL( primaryid ) AND post.threadid = thread.threadid
ORDER BY post.dateline DESC
LIMIT 6
Is there something I'm missing that can help speed this up? It's currently taking my vbindex homepage from a 0.3-0.6 second generation time to a 2 - 8 second generation time. :(
I'm trying to do a "Latest Images" type hack for my forums and while I've seen the other "Recent Attachments" hack that was posted for vB3, it doesn't account for forum permissions or deleted posts, so I thought I'd make my own.
I'm getting a bit hung up, however, on a slow query and I was wondering if anyone else may be able to help shed some light on the matter.
I know that MySQL JOINs can sometimes slow things down as is evident here:
SELECT post.userid, post.username, post.postid, deletionlog.primaryid, thread.title, thread.forumid, attachment.postid, attachment.filename, attachment.thumbnail_dateline
FROM post AS post, thread AS thread
LEFT JOIN attachment AS attachment ON ( attachment.postid = post.postid )
LEFT JOIN deletionlog AS deletionlog ON ( deletionlog.primaryid = post.postid )
WHERE post.attach > 0 AND attachment.thumbnail_dateline > 0 AND thread.forumid NOT
IN (
".implode(',', $limitfids)."
) AND ISNULL( primaryid ) AND post.threadid = thread.threadid
ORDER BY post.dateline DESC
LIMIT 6
Is there something I'm missing that can help speed this up? It's currently taking my vbindex homepage from a 0.3-0.6 second generation time to a 2 - 8 second generation time. :(