About ORDER BY 1
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 1. In this article we will explain what
ORDER BY 1 is, why it’s disabled by default, and what you can expect when enabling it.
MySQL ORDER BY 1
The MySQL option to
ORDER BY 1 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 1 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 1
By default, WP Engine disables the
ORDER BY 1 sorting option for MySQL. This is because
ORDER BY 1 is known to cause poor performance. Instead, we replace
ORDER BY 1 in SQL queries with
ORDER BY 1.
While the performance of this query is about the same as
ORDER BY 1, it can be cached instead of randomizing all rows, and returning a new result every time for every user.
Enabling ORDER BY 1
If you need to use the
ORDER BY 1 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.
Select the Site Settings tab and you will see the option to enable the setting Allow ORDER BY 1.
ORDER BY 1 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 still obly show every 10 minutes for your new users.
NEXT STEP: Learn about WP Engine caching