[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 changes-requested  |     Focuses:  performance
-------------------------------------+--------------------------
Changes (by SirLouen):

 * keywords:  needs-testing has-unit-tests has-patch early => early changes-
     requested


Comment:

 == Combined Issue Reproduction and Patch Test Report
 === Description
 ❌ This report can't validate that the indicated patch works as expected.

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

 === Environment
 - WordPress: 6.9-alpha-60093-src
 - PHP: 8.4.6
 - Server: nginx/1.27.5
 - Database: mysqli (Server: 8.4.5 / Client: mysqlnd 8.4.6)
 - Browser: Chrome 135.0.0.0
 - OS: Windows 10/11
 - Theme: Twenty Twenty-Five 1.2
 - MU Plugins: None activated
 - Plugins:
   * Safe SVG 2.3.1
   * Test Reports 1.2.0
   * WP Query Benchmarking 1.0

 === Expected Results with Patch

 1. Random sort works.
 2. Benchmark indicates improvement.

 === Actual Results

 1. ❌ Random sort fails with the patch
 2. ✅ There is a slight performance improvement

 === Additional Notes

 There are some things wrong with this patch:

 - Firstly, as shown here, there is no randomization with this patch:
 Before (without patch): https://streamable.com/p6mjct
 After (with patch): https://streamable.com/dnf6ob

 - Secondly, as we can see in the screenshots attached, the performance is
 slightly improved.

 - Third, the unit test is always returning OK because it's technically not
 checking for randomized queries adequately. Ideally, it should generate a
 ton more posts because with such little size: 10 interactions for only 3
 units. In case that the unit test was right, it could be triggering false
 asserts once in a while in the automated protocol (i.e. GitHub Actions).
 This could be awful because people, unrelated to this patch, could go
 crazy trying to figure out why this test actually failed for them on an
 unrelated topic, so minimizing or completely ditching this is ideal in
 this case. Apart to that, as I say, the test itself wrong because it
 triggers and fully breaks too early.

 - Finally, there are already some unit tests aimed to `orderby` rand like
 `test_orderby()` so this new test could be, moreover, redundant.

 Further research on this algorithm is required.

 === Supplemental Artifacts
 - Without the patch: https://i.imgur.com/0RPY1sg.png
 - With the patch: https://i.imgur.com/uREFVyH.png

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