From cffdc8efff9f0c66e6640a42fc375b4662b44180 Mon Sep 17 00:00:00 2001 From: jasonmarlin Date: Tue, 15 Jan 2019 11:31:38 -0500 Subject: [ticket/15941] Replace MAX to speed up query in update_post_information Replace MAX SQL function with faster query using order by and limit. The ajacent query could also be optimized to eliminate the usage of MAX. Note that adding a compound key as suggested by EXPLAIN SQL yields an improvement, but not nearly as fast as ORDER + LIMIT. PHPBB3-15941 --- phpBB/includes/functions_posting.php | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/functions_posting.php') diff --git a/phpBB/includes/functions_posting.php b/phpBB/includes/functions_posting.php index c7d691287c..3640f543d9 100644 --- a/phpBB/includes/functions_posting.php +++ b/phpBB/includes/functions_posting.php @@ -202,11 +202,13 @@ function update_post_information($type, $ids, $return_update_sql = false) if (count($ids) == 1) { - $sql = 'SELECT MAX(p.post_id) as last_post_id + $sql = 'SELECT p.post_id as last_post_id FROM ' . POSTS_TABLE . " p $topic_join WHERE " . $db->sql_in_set('p.' . $type . '_id', $ids) . " $topic_condition - AND p.post_visibility = " . ITEM_APPROVED; + AND p.post_visibility = " . ITEM_APPROVED . " + ORDER BY p.post_id DESC"; + $result = $db->sql_query_limit($sql, 1); } else { @@ -216,8 +218,8 @@ function update_post_information($type, $ids, $return_update_sql = false) $topic_condition AND p.post_visibility = " . ITEM_APPROVED . " GROUP BY p.{$type}_id"; + $result = $db->sql_query($sql); } - $result = $db->sql_query($sql); $last_post_ids = array(); while ($row = $db->sql_fetchrow($result)) -- cgit v1.2.1