Fixed Cache or optimise recent comments block query

Sim

Well-known member
I just finished a test import of ZooChat from vB3.8+PhotoPost to XenForo+XFMG, with 423K posts and 277K photos, including 2,219 galleries. My initial testing is quite positive - no major issues so far, even with large numbers of galleries.

However, I've found that the Media home page is taking 5-6 seconds to load and investigating the queries it seems that the recent comments query is very slow.

I think the culprit is the 219K comments we have in the gallery :eek:

Code:
SELECT comment.*, media.media_title, media.media_type, media.media_id, media.media_state, media.attachment_id, media.media_tag, media.category_id,
    album.album_title, album.album_description, albumviewperm.access_type, albumviewperm.share_users, album.album_id, album.album_state, album.album_user_id, album.album_thumbnail_date, user.*, container.album_state AS albumstate,
    attachment.data_id, data.filename, data.file_size, data.file_hash, data.file_path, data.width, data.height, data.thumbnail_width, data.thumbnail_height
FROM xengallery_comment AS comment
LEFT JOIN xengallery_media AS media ON
    (comment.content_id = media.media_id AND comment.content_type = 'media')
LEFT JOIN xf_attachment AS attachment ON
    (attachment.attachment_id = media.attachment_id)
LEFT JOIN xf_attachment_data AS data ON
    (data.data_id = attachment.data_id)
LEFT JOIN xengallery_album AS album ON
    (comment.content_id = album.album_id AND comment.content_type = 'album')
LEFT JOIN xengallery_album_permission AS albumviewperm ON
    (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
LEFT JOIN xengallery_album AS container ON
    (container.album_id = media.album_id)
LEFT JOIN xf_user AS user ON
    (comment.user_id = user.user_id)
LEFT JOIN xengallery_shared_map AS shared ON
    (shared.album_id = COALESCE(album.album_id, media.album_id) AND shared.shared_user_id = 1)
LEFT JOIN xengallery_private_map AS private ON
    (private.album_id = COALESCE(album.album_id, media.album_id) AND private.private_user_id = 1)
WHERE (container.album_state IS NULL OR container.album_state = 'visible' OR album.album_state = 'visible')
    AND user.is_banned = 0
    AND (media.media_state IS NULL OR media.media_state = 'visible')
    AND (album.album_state IS NULL OR album.album_state = 'visible')
    AND (1=1)
    AND comment.comment_state = 'visible'
ORDER BY comment.comment_date DESC
 LIMIT 5

Run Time: 5.946273

2016-10-12_11-41-49.webp

From my testing, it's the album joins which are adding all the time - without them, the query runs in milliseconds.

I may have to create my own custom block using a query that doesn't rely on albums, but it would be good if you could revisit your query to see if there's any optimisation which can be done. I believe the offending query lives in XenGallery_Model_Comment::getCommentsForBlockOrFeed.

Perhaps even just an option to ignore albums (and thus remove those joins completely) would do the job.
 
This is likely some missing indexes which is causing bad optimizer behaviour. Troubleshooting this is often "fun".

The problem is xengallery_comment.user_id is being used instead of xengallery_comment.comment_date (or this colume is missing an index, or is part of a multi-column index which has bad ordering and can't be used)
 
@Sim, are you in a position to test a change here?

library/XenGallery/Model/Comment.php

Find the "getCommentsForBlockOrFeed" function.

Find (line 167):
PHP:
FROM xengallery_comment AS comment

Replace:
PHP:
FROM xengallery_comment AS comment FORCE INDEX(comment_date)

If that solves your problem, which we think it will, we'll roll that out for the next release.
 
Top Bottom