[wp-trac] [WordPress Trac] #18836: ORDER BY RAND() is slow

[WordPress Trac]

#18836: ORDER BY RAND() is slow
-------------------------------------------------+-------------------------
 Reporter:  scribu                               |       Owner:  pbearne
     Type:  enhancement                          |      Status:  assigned
 Priority:  normal                               |   Milestone:  6.9
Component:  Query                                |     Version:
 Severity:  minor                                |  Resolution:
 Keywords:  early has-patch has-test-info dev-   |     Focuses:
  feedback changes-requested                     |  performance
-------------------------------------------------+-------------------------
Changes (by peterwilsoncc):

 * keywords:  early has-patch has-test-info dev-feedback => early has-patch
     has-test-info dev-feedback changes-requested


Comment:

 == Test Report
 === Description
 This report shows that the intended patch does not work as expected.

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

 Modified with a fix for the `$rand_factor` value as noted in
 [https://github.com/WordPress/wordpress-
 develop/pull/8715#discussion_r2286654870 PR#8715 (comment)]

 The rand.php plugin in the MU Plugins list below modifies the main query
 to randomize the posts for the main query

 {{{#!php
 <?php
 add_filter( 'pre_get_posts', function( $query ) {
     if ( ! is_admin() && $query->is_main_query() ) {
         $query->set( 'orderby', 'rand' ); // rand(3) used on `trunk`
     }
 } );
 }}}

 When testing the pagination for the patch, I also tested with a hard coded
 seed value per this comment [https://github.com/WordPress/wordpress-
 develop/pull/8715/files#r2286689658 PR#8715 (comment)]

 === Environment
 - WordPress: 6.9-alpha-60093-src
 - PHP: 8.3.23
 - Server: nginx/1.18.0
 - Database: mysqli (Server: 8.0.36-0ubuntu0.22.04.1 / Client: mysqlnd
 8.3.23)
 - Browser: Firefox 141.0
 - OS: macOS
 - Theme: Twenty Twenty-Five 1.3
 - MU Plugins:
   * cpt-test.php
   * rand.php
   * _admin_bar_show_cache.php
   * _qm-mappings.php
 - Plugins:
   * Query Monitor 3.19.0
   * Test Reports 1.2.0

 === Actual Results
 1.  ⛔️ Patch introduces pagination bugs, preventing the full display of
 posts
 2. The patch does not show the full list of posts when paginating through
 the posts index (`trunk` shows 10 pages, the patch shows 3 pages).
 3. Unlike `ORDERBY rand(seed)` the patch does not ensure that all posts
 are shown as a user navigates all pages.
 4. While testing, I am not seeing faster queries but my strong suspicion
 is that is because I have 100 posts rather than many thousands of posts.

 === Additional Notes

 WP CLI commands to generate posts:

 {{{
 wp site empty
 wp post generate --post_type=post
 for i in {1..100}; do wp post update $i --post_title="Post $i"; done;
 }}}

 Steps taken to test.

 1. Navigate through the full list of pages on the post index
 2. Record the titles of posts displayed on each page, copying them to a
 text file
 3. Sort the text file in ascending order

 To record the list of posts on each page, I ran the following in the JS
 console (Firefox)

 {{{#!js
 var p = 'PAGE ' + $$( '.wp-block-query-pagination-numbers span.current'
 )[0].innerText + '\n'; $$( '.wp-block-post-title' ).forEach( e => { p = p
 + e.innerText + '\n'  } ); console.log( p )
 }}}

 On `trunk`, the full set of posts was displayed over ten pages. No
 repeated posts were shown.

 Running the patch, only three pages of posts were displayed. This included
 a repeated posts displayed on previous pages.

 === Supplemental Artifacts

 See gist of posts shown on each page and in the files `*-sorted.txt`, the
 Post titles sorted in to highlight any duplicates

 https://gist.github.com/peterwilsoncc/7da6f912c155ee8991eea64be501c130

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