mysql - Do Seeded Orderby Rand Queries Benefit from DB Caching?

admin管理员组

文章数量:1122832

I'm aware that various object caching plugins give you the benefit of caching results from frequent database queries. So that repeated queries return a cached result set rather than running the query again.

I've also heard that using Orderby => Rand repeats queries and doesn't allow caching because the results change every time.

Am I correct in assuming that if I use the seed rand(x) Orderby, such as a date as an integer, I can benefit from caching? Has anyone tested this?

I'm aware that various object caching plugins give you the benefit of caching results from frequent database queries. So that repeated queries return a cached result set rather than running the query again.

I've also heard that using Orderby => Rand repeats queries and doesn't allow caching because the results change every time.

Am I correct in assuming that if I use the seed rand(x) Orderby, such as a date as an integer, I can benefit from caching? Has anyone tested this?

• mysql
• cache

Share Improve this question asked Apr 15, 2024 at 14:59 RodneyUKRodneyUK 1 4

• In my experience randomness is a lazy escape way from deciding what logic should be applied when having to show some posts. If it really do not matter you might as well display the latest ones. – Mark Kaplun Commented Apr 16, 2024 at 6:05
• Ordering refers to a custom post type rather than posts, business listings which should be fairly randomised periodically so that everyone gets a fair share of the "first page" – RodneyUK Commented Apr 16, 2024 at 10:09
• and how do you know if its "fair share" ;) ? displaying something when there is no traffic is not the same as displaying when its top traffic. Anyway some sort of "sliding window" will make sure everybody gets to be displayed once in a cycle, and probably easier to implement, at least without the need to worry about cache stability and the need to invalidate it – Mark Kaplun Commented Apr 16, 2024 at 12:42
• The work around I found for this was to maintain an array of IDs for 'ads' that I would update when a new ad is created or an old one is deleted/unpublished. The array is loaded as a string of IDs separated by commas, I then convert that into an actual array using JS and then run a randomizer in JS to select one ID, then use AJAX to call all the data for that one. It's lighting quick. – Tony Djukic Commented Apr 22, 2024 at 21:46

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Looking at the source of class-wp-query.php (ie, the WP_Query code), I find this:

        /*
         * Ensure the ID database query is able to be cached.
         *
         * Random queries are expected to have unpredictable results and
         * cannot be cached. Note the space before `RAND` in the string
         * search, that to ensure against a collision with another
         * function.
         *
         * If `$fields` has been modified by the `posts_fields`,
         * `posts_fields_request`, `post_clauses` or `posts_clauses_request`
         * filters, then caching is disabled to prevent caching collisions.
         */
        $id_query_is_cacheable = ! str_contains( strtoupper( $orderby ), ' RAND(' );

...which suggests that, even with seeded randomness à la RAND(x), the query won't get cached.

本文标签： mysqlDo Seeded Orderby Rand Queries Benefit from DB Caching