Fixed Unexpectedly expensive query in Thread::actionNewPosts for long threads

[Xon]

Affected version

2.1.8

In XF\Pub\Controller\Thread::actionNewPosts (and a few other 'next-post' like methods), there is a call to findNewestPostsInThread (this applies to findNextPostsInThread) which finds the next (or previous) post by post date. this query can be unexpectedly expensive for long threads due to the sort order and index selection.

A simple tweak to the 'order by' clause goes from ~150ms down to ~0.08ms for this query, and helps avoid unexpected latency spikes due to cold caches for threads.

$finder
   ->inThread($thread, $limits)
   ->orderByDate('DESC')
   ->newerThan($newerThan);

with orderByDate being;

$this->order('position', $direction)->order('post_date', $direction);

Spoiler: sql example & query output

analyze format=json
SELECT `xf_post`.*
FROM `xf_post`
WHERE (`xf_post`.`thread_id` = 44838) AND ((`xf_post`.`message_state` = 'moderated' AND `xf_post`.`user_id` = 20190) OR (`xf_post`.`message_state` IN ('visible'))) AND (`xf_post`.`post_date` > 1570145112)
ORDER BY `xf_post`.`position` DESC, `xf_post`.`post_date` DESC
LIMIT 4;

outputs;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 151.07,
    "read_sorted_file": {
      "r_rows": 4,
      "filesort": {
        "sort_key": "xf_post.position desc, xf_post.post_date desc",
        "r_loops": 1,
        "r_total_time_ms": 151.05,
        "r_limit": 4,
        "r_used_priority_queue": true,
        "r_output_rows": 5,
        "table": {
          "table_name": "xf_post",
          "access_type": "range",
          "possible_keys": [
            "thread_id_post_date",
            "thread_id_position",
            "user_id",
            "post_date"
          ],
          "key": "thread_id_post_date",
          "key_length": "8",
          "used_key_parts": ["thread_id", "post_date"],
          "r_loops": 1,
          "rows": 172722,
          "r_rows": 89620,
          "r_total_time_ms": 135.03,
          "filtered": 100,
          "r_filtered": 99.272,
          "index_condition": "xf_post.thread_id = 44838 and xf_post.post_date > 1570145112",
          "attached_condition": "xf_post.user_id = 20190 and xf_post.message_state = 'moderated' or xf_post.message_state = 'visible'"
        }
      }
    }
  }
}

analyze format=json
SELECT `xf_post`.*
FROM `xf_post`
WHERE (`xf_post`.`thread_id` = 44838) AND ((`xf_post`.`message_state` = 'moderated' AND `xf_post`.`user_id` = 20190) OR (`xf_post`.`message_state` IN ('visible'))) AND (`xf_post`.`post_date` > 1570145112)
ORDER BY `xf_post`.`post_date` DESC
LIMIT 4;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0756,
    "table": {
      "table_name": "xf_post",
      "access_type": "range",
      "possible_keys": [
        "thread_id_post_date",
        "thread_id_position",
        "user_id",
        "post_date"
      ],
      "key": "thread_id_post_date",
      "key_length": "8",
      "used_key_parts": ["thread_id", "post_date"],
      "r_loops": 1,
      "rows": 172722,
      "r_rows": 4,
      "r_total_time_ms": 0.0501,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "xf_post.thread_id = 44838 and (xf_post.user_id = 20190 and xf_post.message_state = 'moderated' or xf_post.message_state = 'visible') and xf_post.post_date > 1570145112"
    }
  }
}

The query is filtering on thread_id & post_date, and as such picks the thread_id_post_date index. However, since it needs to sort by position; it can not apply the limit clause on an index query.

Simple fix another method besides the incorrectly named orderByDate function should be used which just sets the order-by clause to the date and not the position field.

 

[XF Bug Bot]

Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.1.10).

Change log:

Make query for finding newest/next posts in a thread more performant.

There may be a delay before changes are rolled out to the XenForo Community.