ORDER BY RAND() is slow

[scribu]

WP_Query currently accepts 'orderby' => 'rand' which translates to ORDER BY RAND().

This is very slow when you have many posts, since it effectively calls RAND() for each row.

A faster way would be to call RAND() only once and put it in the LIMIT clause.

The only thing is that we have to make sure that the generated number is smaller than (total number of posts - number of posts to fetch).

So, this would require to do an extra query to calculate the total. It should still be faster than the current method.

If we want to get more than one post, we can get them in any order and then call shuffle() on the resulting array.

[scribu]

Here's a method of doing it all in a single query:

​http://explainextended.com/2009/03/01/selecting-random-rows/

Not sure how feasible it would be for WP.

[hakre]

​http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand

[wonderboymusic]

A patch for this would be fun.

[MisdaX]

first attempt - passes all phpunit standard tests

[MisdaX]

Tests with MySQL EXPLAIN shows no creation of temporary tables. The execution time with ~900 posts is also 2-3 times faster.

I ran the standard phpunit tests - no additional error was found.

[johnbillion]

Looks like your patch got missed, MisdaX. Sorry about that.

Can we confirm which unit tests cover the ORDER BY RAND() functionality please? Not that it's really very testable, but still.

[MisdaX]

It is indeed difficult. I tried to reduce the impact of the patch as best as I can. I tested a vanilla wordpress and a patched wordpress with the whole unit test package to ensure that I broke nothing.

I assumed that there are tests which cover the ordering part of the function. No change in the result of the unit tests was a sign for me that everything should be ok. Unfortunately I have no idea how I can provide a suitable test for a random order functionality :-)

Independently, it would be nice if someone could provide a performane test with a big database.

This update modifies the 'test_orderby' function assertion to accommodate expected 'rand()' value in the SQL query request. An additional unit test 'test_wp_query_orderby_rand' is also introduced to verify that the 'orderby' => 'rand' parameter provides seemingly random results upon multiple requests.

Trac ticket: 18836

[pbearne]

Hi

I created a test to check we get a random order.

but the code provided in the patch did not provide a random order so more work is needed.

[pbearne]

may put into another ticket

[audrasjb]

As per today's bug scrub: It's a bit late in the release cycle to introduce Query/DB changes, so I'm moving it to 6.9 with the early keyword.

Please note that there is a WPCS issue in the current PR.

[adamsilverstein]

We discussed this today in the Performance bug scrub and the question came up: is this still true: "ORDER BY RAND() is slow" or has this issue been resolved already?

[pbearne]

@adamsilverstein I don't know It may have already fixed upstream

[SirLouen]

I've passed my WP-Query benchmarking template and here are the results:

These are the 3 queries benchmarked in a pool of 10K posts, $post_per_page = 1K, 10 iterations each

new WP_Query([
    'post_type' => 'post',
    'posts_per_page' => $posts_per_page,
    'orderby' => 'rand',
    'no_found_rows' => true,
    'cache_results' => false
]);

new WP_Query([
    'post_type' => 'post',
    'posts_per_page' => $posts_per_page,
    'orderby' => 'date',
    'order' => 'DESC',
    'no_found_rows' => true,
    'cache_results' => false
]);

new WP_Query([
    'post_type' => 'post',
    'posts_per_page' => $posts_per_page,
    'orderby' => 'title',
    'order' => 'DESC',
    'no_found_rows' => true,
    'cache_results' => false
]);

Tomorrow I will be checking the new implementation pushed by @pbearne to see if it actually improves the rand implementation and create a proper Patch testing report.

[SirLouen]

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:
  • Test Reports 1.2.0
  • WQROC 1.0.0
  • WP Query Benchmarking 1.0.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://f003.backblazeb2.com/file/wordpress-videos/wp-videos/2025/05/18836.1.mp4
After (with patch): ​https://f003.backblazeb2.com/file/wordpress-videos/wp-videos/2025/05/18836.2.mp4

• 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

[SirLouen]

Adding more beef to the grill, I completely forgot the most obvious test… number of queries

This is without any modifications, just bare queries like the mentioned here

After all this testing, I'm starting to think that this ticket is completely obsolete by now.

With all this info provided, I would add this as a candidate for close ⇒ wont-fix

[SirLouen]

After hour and a half trying to understand ​the algorithm, I think I've come with a solution with the idea that @scribu was suggesting one decade ago, and I think I got it.

The performance gains are important:

I'm still double-checking the code, but I will provide a patch tomorrow, just wanted to remove the close proposal because maybe there is an opportunity for optimization here.

Test Demo: ​https://f003.backblazeb2.com/file/wordpress-videos/wp-videos/2025/05/18836.3.mp4

Brief Testing Info Ideas

1. First you might need to publish a lot of posts. The more, the better, 10 for example
2. Use a Query like:

   $query = new WP_Query([
               'posts_per_page' => 10, 
               'fields' => 'ids',
               'post_status' => 'publish',
               'orderby' => 'ID',
               'order' => 'ASC'
   ]);
   

3. And fix the result (because you need to check if future WP_Queries return the same order or not)

$fixed_ids = $query->posts;

4. Finally we generate another WP_Query, but this time with the orderby and rand for the exact same posts we got previously. Do this multiple times, check generation order to see if there is some sort of randomness in each generation.

$query = new WP_Query([
        'post__in' => $fixed_ids,
        'posts_per_page' => 10,
        'orderby' => 'rand',
        'post_status' => 'publish',
        'ignore_sticky_posts' => true,
        'fields' => 'ids',
    ]);

5. For the performance part, use microtime and get_num_queries() accompanied by define( 'SAVEQUERIES', true );

Trac ticket: https://core.trac.wordpress.org/ticket/18836

[SirLouen]

Note that I have ignored the unit-tests because there are already unit-test for orderby rand in ::test_orderby()

[pbearne]

Replying to SirLouen:

Note that I have ignored the unit-tests because there are already unit-test for orderby rand in ::test_orderby()

Good work @SirLouen

No, let's get this in core

[SirLouen]

Testing Instructions Improved

1. Use this plugin in a new clean environment: ​https://github.com/SirLouen/wp-query-benchmarking-v2
2. First generate 10K posts to have a populated DB
3. Run the performance tests with and without the Patch.

You are welcome to review and improve the plugin to add more test cases or improve the mock data.

[nikunj8866]

Test Report

Description

This report confirms that the proposed patch resolves the issue as described.

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

Environment

• WordPress: 6.8.1
• PHP: 8.2.27
• Server: nginx/1.26.1
• Database: mysqli (Server: 8.0.35 / Client: mysqlnd 8.2.27)
• Browser: Chrome 136.0.0.0
• OS: Windows 10/11
• Theme: Twenty Twenty-Five 1.2
• MU Plugins: None activated
• Plugins:
  • Test Reports 1.2.0
  • WP Query Benchmarking 1.0.0

Actual Results

1. ✅ Issue resolved with patch.

Additional Notes

Supplemental Artifacts

Before Patch:
​https://prnt.sc/QW1dLAMzbRX5

After Patch:
​https://prnt.sc/sCA5yG5U5Jyb

[peterwilsoncc]

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 ​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
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 ​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)

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

[SirLouen]

@peterwilsoncc feel free to send some commits to my PR if you think you can push it forward.

I was working on this when I started testing mostly all the tickets in Trac available in the Testing Team list. This particularly caught my attention because testing was a little tricky and got me to a possible solution.

The thing is that I'm not specialized in Query component and I'm focusing rn in other components, plus since this has stalled for a couple months I've lost all my momentum on this ticket (I would have to re-read all the docs about this algo in Stack Overflow, I barely remember now).

If you don't have time to work on this either, I would de-Milestone it to Future Release

[peterwilsoncc]

@SirLouen I won't have time for 6.9 so I've moved it as suggested.

...I would have to re-read all the docs about this algo in Stack Overflow...

Basically the same problem here, but I'd be going in completely fresh :)

If inspirations hits at any point, I'll push to your branch as suggested. Thanks