1. wedge1001

    wedge1001 - Aug 1, 2017 New Member

    Credit
    Point
    Hello,

    i've purchased this addon to automate the promotion-posts.
    Unfortionately I wasn't able to install it.

    After doing some debugging i've found out, that the query in Model/Post.php takes way too much time.
    e.g. the initial setup will take around 8 Minutes on my board - thus leading to 504-errors.
    This is due to the fact, that you join all Posts in the board to this table. So on very large boards, this will take ages.

    Every Post will take additional ~25s till ~30s to post for the most popular posters on my board (around 25k-30k Posts).
    Thus leading to database-deadlocks.

    in Order to fix this, i've rewritten more or less the whole class. (Sorry to highjack your plugin)

    Code:
    class Brivium_ExtraPromotions_Model_Post extends XenForo_Model
    {
    ...
                $where = '';
                $xf_postWhere = '';
            }else
            {
                $where = 'WHERE xf_user.user_id IN ('.$db->quote($userIds).')';
                $xf_postWhere = ' AND xf_post.user_id IN ('.$db->quote($userIds).') ';
            }
            $db->query('
                UPDATE xf_user
                LEFT JOIN (
                    SELECT user_id, post_date AS last_post_date
                    FROM xf_post
                    WHERE message_state = '.$db->quote('visible').'
                    '.$xf_postWhere.'
                    ORDER BY post_date desc
                    LIMIT 1
                ) AS post ON (post.user_id = xf_user.user_id)
                SET
                    xf_user.brep_last_posted_date = IF(post.last_post_date IS NULL, 0, post.last_post_date)
                '.$where.'
    ...
    This will decrease the additional time for the 25k-users from 25s to 0.01s

    though i haven't tested the initial installation - but it should work.
     
    Loading...
  2. Lisa Nguyen

    Lisa Nguyen - Aug 1, 2017 Brivium Tester Staff Member

    Credit
    Point
    Oh, Don't worry. Thank you so much for your great feedback to improve it. We are going to release the update version for this add-on. Once again, many thanks.
     
    #2
  3. wedge1001

    wedge1001 - Aug 1, 2017 New Member

    Credit
    Point
    please try a fresh install.
    i'm not sure if the limit 1 shouldn't be put to the where clause, in order to get the full table-join.
     
    #3