Bob
Well-known member
That widget is made with the Image Gallery renderer for the [bd] Widget Framework that comes with Showcase.
Recent / Random Attachments On Forum Homepage [Paid] [Deleted]
- Thread starter Brent W
- Start date
That widget is made with the Image Gallery renderer for the [bd] Widget Framework that comes with Showcase.
Yeah,
But the hyperlink goes to the Gallery, not to a thread
I just wanted to show it as the "example" of a great idea to have as a widget
Brent W
Well-known member
I am not sure if this is a default xenForo query or not but this is one I found with 8 seconds+ run time
Code:
# Time: 130817 4:36:26
# User@Host: <redacted>[<redacted>] @ localhost []
# Query_time: 8.823284 Lock_time: 0.000019 Rows_sent: 5 Rows_examined: 1830082
SET timestamp=1376732186;
SELECT attachment.*, attachment_data.*^M
,^M
post.thread_id,^M
thread.*^M
FROM xf_attachment AS attachment^M
INNER JOIN xf_attachment_data AS attachment_data ON^M
(attachment_data.data_id = attachment.data_id)^M
^M
INNER JOIN xf_post AS post ON^M
(post.post_id = attachment.content_id)^M
LEFT JOIN xf_thread AS thread ON^M
(thread.thread_id = post.thread_id)^M
WHERE (attachment.content_type = 'post')AND (thread.node_id IN ('8', '854', '855', '856', '857', '328', '96', '343', '462', '344', '58', '345', '463', '346', '72', '347', '461', '348', '292', '349', '460', '350', '86', '351', '464', '352', '339', '385', '649', '650', '651', '652', '665', '666', '667', '668', '57', '342', '522', '623', '639', '640', '641', '624', '625', '626', '675', '629', '698', '630', '237', '11', '95', '26', '628', '709', '612', '613', '658', '661', '660', '659', '662', '663', '620', '634', '635', '636', '637', '638', '671', '686', '234', '170', '19', '20', '21', '82', '83', '94', '76', '22', '23', '619', '687', '688', '691', '692', '693', '694', '690', '689', '647', '656', '677', '679', '680', '657', '669', '678', '813', '681'))AND (attachment_data.thumbnail_width != 0)^M
ORDER BY attachment_data.upload_date DESC^M
LIMIT 5;
Brent W
Well-known member
That is funny. Thanks.
xf_phantom
Well-known member
It was just a guess, because that's definitly no default xenforo query .
xf_phantom
Well-known member
you could also optimize the query
Brent W
Well-known member
So now that it isn't 5AM and I can think clearly... this was the cause of pretty large server load spikes on my server. I have spent the last two weeks trying to figure out where it was coming from and finally got the slow query log setup and working.
These queries were all over the place and are more frequent now because the site in question is getting more traffic and is pretty large. After disabling it I rarely get the 10-15 load spikes and everything stays right at or under 1. So just a heads up to large sites with a large amount of attachments, this can be quite the mess for servers. Hopefully we can cache it or come up with a better quest in the future.
These queries were all over the place and are more frequent now because the site in question is getting more traffic and is pretty large. After disabling it I rarely get the 10-15 load spikes and everything stays right at or under 1. So just a heads up to large sites with a large amount of attachments, this can be quite the mess for servers. Hopefully we can cache it or come up with a better quest in the future.
If you're in agreement...
I believe from the query that you're using recent attachments rather than random... and if that's the case, IMHO, the ordering by upload_date isn't important. If it's the most recent 5, then that 5 will be in an appropriate order already.
So...
In RecentAttachmentsBlock/Listener.php change Line 53 from:
to:
From my tests, albeit with only a few attachments, the run time for the query is considerably faster.
Let me know how it works in your environment.
I believe from the query that you're using recent attachments rather than random... and if that's the case, IMHO, the ordering by upload_date isn't important. If it's the most recent 5, then that 5 will be in an appropriate order already.
So...
In RecentAttachmentsBlock/Listener.php change Line 53 from:
PHP:
$fetchOptions += array(
'limit' => $limit,
'order' => 'recent'
);to:
PHP:
$fetchOptions += array(
'limit' => $limit
);From my tests, albeit with only a few attachments, the run time for the query is considerably faster.
Let me know how it works in your environment.
MattW
Well-known member
Before:
Showing rows 0 - 4 (5 total, Query took 0.1402 sec) [upload_date: 1376758210 - 1376692854]
After:
Showing rows 0 - 4 (5 total, Query took 0.0028 sec)
That's quite an improvement.
MattW
Well-known member
Can also make it a bit quicker by swapping the INNER JOIN with a LEFT JOIN
With ORDER BY still in place, but swapping to LEFT JOIN
Showing rows 0 - 4 (5 total, Query took 0.0865 sec) [upload_date: 1376758210 - 1376692854]
and removing ORDER BY as well
Showing rows 0 - 4 (5 total, Query took 0.0018 sec)
With ORDER BY still in place, but swapping to LEFT JOIN
Showing rows 0 - 4 (5 total, Query took 0.0865 sec) [upload_date: 1376758210 - 1376692854]
and removing ORDER BY as well
Showing rows 0 - 4 (5 total, Query took 0.0018 sec)
Yep.
So change line 63 of RecentAttachmentsBlock/Model/Attachment.php
From:
To:
Do that in conjunction with the previous code edit, and I think that's pretty optimised. Might be able to do a bit more in terms of caching -- especially when dealing with recent vs random.
So change line 63 of RecentAttachmentsBlock/Model/Attachment.php
From:
PHP:
INNER JOIN xf_attachment_data AS attachment_data ONTo:
PHP:
LEFT JOIN xf_attachment_data AS attachment_data ONDo that in conjunction with the previous code edit, and I think that's pretty optimised. Might be able to do a bit more in terms of caching -- especially when dealing with recent vs random.
MattW
Well-known member
I also replaced
PHP:
INNER JOIN xf_post AS post ON
PHP:
LEFT JOIN xf_post AS post ONBrent W
Well-known member
MattW
Well-known member
@Chris Deeming - just checked this again, and removing the order by date will get you the oldest attachments, not the latest.
Those are from my phpBB3 install back in 2007.
Good stuff 
Change that entire function, then, to this:
Change that entire function, then, to this:
PHP:
public function getAttachmentsWithAttachKey(array $conditions = array(), array $fetchOptions = array())
{
$whereConditions = $this->prepareAttachmentConditions($conditions, $fetchOptions);
$limitOptions = $this->prepareLimitFetchOptions($fetchOptions);
return $this->fetchAllKeyed($this->limitQueryResults(
'
SELECT attachment.*, attachment_data.*,
post.thread_id, thread.*
FROM xf_attachment AS attachment
LEFT JOIN xf_attachment_data AS attachment_data ON
(attachment_data.data_id = attachment.data_id)
LEFT JOIN xf_post AS post ON
(post.post_id = attachment.content_id)
LEFT JOIN xf_thread AS thread ON
(thread.thread_id = post.thread_id)
WHERE ' . $whereConditions . '
ORDER BY attachment.attachment_id DESC
', $limitOptions['limit'], $limitOptions['offset']
), 'attachment_id');
}
bluemartian
Member
I just purchased this add-on and it is exactly what I need but I'm getting the blurries, too. No matter what dimensions I set, whether I'm in Recent or Random mode. Any suggestions? I've already rebuilt my attachment thumbs cache once and set the add-on to match the attachment dimensions but it makes no difference. They're so blurry it is unusable. Hoping someone can suggest a solution! Thanks.