Make WordPress Core

Opened 13 years ago

Last modified 3 months ago

#18836 assigned enhancement

ORDER BY RAND() is slow

Reported by: scribu's profile scribu Owned by: pbearne's profile pbearne
Milestone: Priority: normal
Severity: minor Version:
Component: Query Keywords: needs-testing has-unit-tests
Focuses: performance Cc:

Description (last modified by 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.

Attachments (1)

18836.diff (976 bytes) - added by MisdaX 9 years ago.
first attempt - passes all phpunit standard tests

Download all attachments as: .zip

Change History (17)

#1 @scribu
13 years ago

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.

#2 @sirzooro
13 years ago

  • Cc sirzooro added

#3 @scribu
13 years ago

  • Description modified (diff)

#4 @c3mdigital
11 years ago

  • Keywords needs-patch added

#6 @nacin
10 years ago

  • Component changed from Performance to Query
  • Focuses performance added

#7 @wonderboymusic
10 years ago

  • Keywords needs-unit-tests added
  • Milestone changed from Awaiting Review to Future Release

A patch for this would be fun.

@MisdaX
9 years ago

first attempt - passes all phpunit standard tests

#8 @MisdaX
9 years ago

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.

#9 @MisdaX
9 years ago

  • Keywords has-patch added; needs-patch removed

#10 @MisdaX
9 years ago

  • Keywords needs-testing added

#11 @johnbillion
9 years ago

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.

#12 @MisdaX
9 years ago

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 ticket was mentioned in PR #6241 on WordPress/wordpress-develop by @pbearne.


3 months ago
#13

  • Keywords has-unit-tests added; needs-unit-tests removed

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

#14 @pbearne
3 months ago

  • Keywords has-patch removed
  • Owner set to pbearne
  • Status changed from new to assigned

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.

Last edited 3 months ago by pbearne (previous) (diff)

#15 @pbearne
3 months ago

may put into another ticket

This ticket was mentioned in Slack in #core-performance by pbearne. View the logs.


3 months ago

Note: See TracTickets for help on using tickets.