Fixed Cache or optimise recent comments block query

[Sim]

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

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

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.

 

[Xon]

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)

 

[Chris D]

We'll see what we can do to optimise this for the next release.

 

[Chris D]

@Sim, are you in a position to test a change here?

library/XenGallery/Model/Comment.php

Find the "getCommentsForBlockOrFeed" function.

Find (line 167):

FROM xengallery_comment AS comment

Replace:

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.

 

[Sim]

Before edit 10.09 seconds for query:



After edit 0.002 seconds for query:




... I think that will do just nicely. Thanks @Chris D !!

 

[Chris D]

Thanks