1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fixed Cache or optimise recent comments block query

Discussion in 'Media Gallery Resolved Bugs' started by Sim, Oct 12, 2016.

  1. Sim

    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:

    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.
  2. Xon

    Xon Well-Known Member

    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)
  3. Chris D

    Chris D XenForo Developer Staff Member

    We'll see what we can do to optimise this for the next release.
    thedude likes this.
  4. Chris D

    Chris D XenForo Developer Staff Member

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


    Find the "getCommentsForBlockOrFeed" function.

    Find (line 167):
    FROM xengallery_comment AS comment
    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.
    thedude, Jake B., Xon and 1 other person like this.
  5. Sim

    Sim Well-Known Member

    Before edit 10.09 seconds for query:


    After edit 0.002 seconds for query:


    ... I think that will do just nicely. Thanks @Chris D !!
    thedude likes this.
  6. Chris D

    Chris D XenForo Developer Staff Member

    Thanks :)

Share This Page