Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.7-PL1
    • Fix Version/s: 3.0.8-RC1
    • Labels:
      None
    • Environment:
      Apache/2.2.15, PHP/5.3.2, MySQL 5.1.45, Iceweasel 3.0.6

      Description

      Splitting topics requires move_posts. Move_posts calls update_post_info before it exits from function_admin.php.

      Update_post_info has a query which takes very-very long time to be executed (sometimes more than 30secs). This is it:

      // Now, let us collect the user/topic combos for rebuilding the information
      $sql = 'SELECT poster_id, topic_id
      FROM ' . POSTS_TABLE . '
      WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
      AND poster_id <> ' . ANONYMOUS . '
      GROUP BY poster_id, topic_id';
      $result = $db->sql_query($sql);

      Is there a way to optimize it somehow?

      Cheers,
      aig

        Activity

        Hide
        aig aig added a comment -

        It turns out that the sql_in_set function leaves some ' chars in the query which make MySQL require ages to return results.

        A quick fix is to replace (file function_admin.php, function update_posted_info):

        $sql = 'SELECT poster_id, topic_id
        FROM ' . POSTS_TABLE . '
        WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
        AND poster_id <> ' . ANONYMOUS . '
        GROUP BY poster_id, topic_id';

        with:

        $sql = 'SELECT poster_id, topic_id
        FROM ' . POSTS_TABLE . '
        WHERE ' . str_replace("'", "", $db->sql_in_set('topic_id', $topic_ids)) . '
        AND poster_id <> ' . ANONYMOUS . '
        GROUP BY poster_id, topic_id';

        Please let me know, if there is another way.

        Cheers,
        aig

        Show
        aig aig added a comment - It turns out that the sql_in_set function leaves some ' chars in the query which make MySQL require ages to return results. A quick fix is to replace (file function_admin.php, function update_posted_info): $sql = 'SELECT poster_id, topic_id FROM ' . POSTS_TABLE . ' WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . ' AND poster_id <> ' . ANONYMOUS . ' GROUP BY poster_id, topic_id'; with: $sql = 'SELECT poster_id, topic_id FROM ' . POSTS_TABLE . ' WHERE ' . str_replace("'", "", $db->sql_in_set('topic_id', $topic_ids)) . ' AND poster_id <> ' . ANONYMOUS . ' GROUP BY poster_id, topic_id'; Please let me know, if there is another way. Cheers, aig
        Hide
        rxu Ruslan Uzdenov added a comment - - edited

        The problem is not in sql_in_set itself but is in move_posts function where $topic_ids array values are not being casted as integers before passing to update_post_info.
        Now compare: if you set $topic_ids = array('1'); you get topic_id = '1' in SQL query, but setting $topic_ids = array(1); you get topic_id = 1 (no apostrophes since 1 is an integer).
        Proposed fix would be to cast all values pulled from DB as they are supposed to be, f.e. replace

        		$forum_ids[] = $row['forum_id'];
        $topic_ids[] = $row['topic_id'];

        with

        		$forum_ids[] = (int) $row['forum_id'];
        $topic_ids[] = (int) $row['topic_id'];

        Show
        rxu Ruslan Uzdenov added a comment - - edited The problem is not in sql_in_set itself but is in move_posts function where $topic_ids array values are not being casted as integers before passing to update_post_info. Now compare: if you set $topic_ids = array('1'); you get topic_id = '1' in SQL query, but setting $topic_ids = array(1); you get topic_id = 1 (no apostrophes since 1 is an integer). Proposed fix would be to cast all values pulled from DB as they are supposed to be, f.e. replace $forum_ids[] = $row['forum_id']; $topic_ids[] = $row['topic_id']; with $forum_ids[] = (int) $row['forum_id']; $topic_ids[] = (int) $row['topic_id'];
        Hide
        bantu Andreas Fischer added a comment -

        As rxu pointed out the single quotes occur because the type of the array values is string (should be int).

        rxu: Do you want to put your change(s) into a git commit that can be merged? If not, just assign it back to me.

        Show
        bantu Andreas Fischer added a comment - As rxu pointed out the single quotes occur because the type of the array values is string (should be int). rxu: Do you want to put your change(s) into a git commit that can be merged? If not, just assign it back to me.
        Hide
        rxu Ruslan Uzdenov added a comment -

        rxu: Do you want to put your change(s) into a git commit that can be merged?

        Sure, I'll do.

        Show
        rxu Ruslan Uzdenov added a comment - rxu: Do you want to put your change(s) into a git commit that can be merged? Sure, I'll do.
        Hide
        bantu Andreas Fischer added a comment -

        Adjusted commit message to be no longer than 80 characters per line as per http://wiki.phpbb.com/display/DEV/Git.

        Show
        bantu Andreas Fischer added a comment - Adjusted commit message to be no longer than 80 characters per line as per http://wiki.phpbb.com/display/DEV/Git .

          People

          • Assignee:
            rxu Ruslan Uzdenov
            Reporter:
            aig aig
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development