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.
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;
And that would print out the title of a random post in my wp_posts table. This is all fine if I only have about 1000 rows in my wp_posts table. But 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. Instead, it replaces “ORDER BY RAND()” in SQL queries with “ORDER BY 1” instead. 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. Because ORDER BY RAND() is known to cause poor performance, the default is to disable it.
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 section (/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().”
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.
- And, 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.