About ORDER BY RAND()

Last updated on June 25th, 2021

If you’ve ever been exploring in the WP Engine section of your WordPress Admin Dashboard, you’ve probably noticed a setting lower down in the “Advanced” section called for Allow ORDER BY RAND(). In this article we will explain what ORDER BY RAND() is, why it’s disabled by default, and what you can expect when enabling it.

Table of Contents

1. MySQL ORDER BY RAND()

2. WP Engine and ORDER BY RAND()

2.1. Enabling ORDER BY RAND()

2.2. Considerations

MySQL ORDER BY RAND()

The MySQL option to ORDER BY RAND() is made to allow users to randomly order items in a list, and use the “LIMIT 1” option to print a randomized item from that list.

For example, if I wanted to print out the post title of a random item in my wp_posts table, I could run the following query:

SELECT `post_title` FROM `wp_posts` ORDER BY RAND() LIMIT 1;

That would print out the title of a random post in my wp_posts table. This is fine if I only have about 1000 rows in my wp_posts table. However, the query becomes exponentially more computationally expensive to run the more rows you have.

Running the query in my wp_posts table of 222 posts, it took 14.1ms, but on a wp_posts table with over 150,000 rows, it took 258ms. If you have a large database and are looking to randomize a result for every single visitor to your website, this can easily slam MySQL with slow queries. This in turn causes slow performance for your end users and potentially cause high CPU load and Memory usage.

WP Engine and ORDER BY RAND()

By default, WP Engine disables the ORDER BY RAND() sorting option for MySQL. This is because ORDER BY RAND() is known to cause poor performance. Instead, we replace ORDER BY RAND() in SQL queries with ORDER BY 1.

While the performance of this query is about the same as ORDER BY RAND(), it can be cached instead of randomizing all rows, and returning a new result every time for every user.

Enabling ORDER BY RAND()

If you need to use the ORDER BY RAND() function in MySQL, you do have the option to enable it. You will find the toggle for this function in your WordPress Admin Dashboard, under the WP Engine plugin tab (/wp-admin/admin.php?page=wpengine-common).

Scroll down to the “Advanced Configuration” section and you will see the option to “allow ORDER BY RAND().”

Considerations

When using ORDER BY RAND() there are some caveats to consider:

Firstly, we recommend caching your randomized result using a Transient. You can set a Transient expiration time of 5-15 minutes. This in turn can help ensure your queries scale with excess traffic.
If you experience any conflicts or unexpected behavior with your Transient, consider turning off Object Caching.
Remember that the page generated by PHP will be cached for 10 minutes in our proprietary page caching system (Evercache). This means a randomized result will show every 10 minutes for your new users.

NEXT STEP: Learn about WP Engine caching