[wp-trac] [WordPress Trac] #61097: Filter `wp_count_posts()` query before execution to avoid slow query

[WordPress Trac]

#61097: Filter `wp_count_posts()` query before execution to avoid slow query
-------------------------------------------------+-------------------------
 Reporter:  rcorrales                            |       Owner:  (none)
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Future
                                                 |  Release
Component:  Posts, Post Types                    |     Version:  2.5
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch has-unit-tests has-test-   |     Focuses:
  info needs-testing                             |  performance
-------------------------------------------------+-------------------------

Comment (by sajjad67):

 == Test Report
 === Description
 This report validates whether the indicated patch works as expected.

 Patch tested: https://github.com/WordPress/wordpress-develop/pull/9772

 === Environment
 - WordPress: 6.9-alpha-60093-src
 - PHP: 8.2.29
 - Server: nginx/1.29.1
 - Database: mysqli (Server: 8.4.6 / Client: mysqlnd 8.2.29)
 - Browser: Chrome 140.0.0.0
 - OS: Windows 10/11
 - Theme: Twenty Twenty-Five 1.3
 - MU Plugins: None activated
 - Plugins:
   * WP Count Posts Tester 1.0
   * Test Reports 1.2.0

 === Actual Results
 1.  ✅ Issue resolved with patch.

 == Before Patch:

 Test (1) Results:

 Starting tests...
 Test 1: 0.868772 seconds
 Test 2: 0.811469 seconds
 Test 3: 0.841343 seconds
 Test 4: 0.844959 seconds
 Test 5: 0.936868 seconds
 Test 6: 0.984893 seconds
 Test 7: 0.992773 seconds
 Test 8: 0.840826 seconds
 Test 9: 0.844373 seconds
 Test 10: 0.832345 seconds

 Average Execution Time: 0.879862 seconds

 Maximum Execution Time: 0.992773 seconds

 Minimum Execution Time: 0.811469 seconds


 Test (2) Results:

 Starting tests...
 Test 1: 0.891964 seconds
 Test 2: 0.842310 seconds
 Test 3: 0.871342 seconds
 Test 4: 0.813705 seconds
 Test 5: 0.826535 seconds
 Test 6: 0.856882 seconds
 Test 7: 0.833914 seconds
 Test 8: 0.839651 seconds
 Test 9: 0.819822 seconds
 Test 10: 0.837396 seconds

 Average Execution Time: 0.843352 seconds

 Maximum Execution Time: 0.891964 seconds

 Minimum Execution Time: 0.813705 seconds

 == After Patch:

 Test (1) Results:

 Starting tests...
 Test 1: 0.179157 seconds
 Test 2: 0.185003 seconds
 Test 3: 0.199899 seconds
 Test 4: 0.176707 seconds
 Test 5: 0.160848 seconds
 Test 6: 0.178117 seconds
 Test 7: 0.167298 seconds
 Test 8: 0.163988 seconds
 Test 9: 0.164013 seconds
 Test 10: 0.162798 seconds

 Average Execution Time: 0.173783 seconds

 Maximum Execution Time: 0.199899 seconds

 Minimum Execution Time: 0.160848 seconds


 Test (2) Results:

 Starting tests...
 Test 1: 0.163915 seconds
 Test 2: 0.166620 seconds
 Test 3: 0.153695 seconds
 Test 4: 0.153376 seconds
 Test 5: 0.149429 seconds
 Test 6: 0.153423 seconds
 Test 7: 0.154962 seconds
 Test 8: 0.172091 seconds
 Test 9: 0.214494 seconds
 Test 10: 0.157147 seconds

 Average Execution Time: 0.163915 seconds

 Maximum Execution Time: 0.214494 seconds

 Minimum Execution Time: 0.149429 seconds

 === Additional Notes
 - Huge difference in execution time
 - 2,000,000 Public Posts & 2,000,000 Private Posts, total (4,000,000
 Posts) was used to test the execution time.

 === Supplemental Artifacts
 [[Image(https://img001.prntscr.com/file/img001/PDM6uqPMQoKdf88tzn8Aog.png)]]

 [[Image(https://img001.prntscr.com/file/img001/5_AHEl-
 5S26AAZSFG4bNAw.png)]]

 [[Image(https://img001.prntscr.com/file/img001/BuXAkWB5QNORRSsn8iJY5Q.png)]]

 [[Image(https://img001.prntscr.com/file/img001/ng-h-Uc4Sa6F1eWCkvxrdA.png)]]

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/61097#comment:16>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform