I have a view and when I use the Random sort it drastically degrades the performance of that view. For instance, using the devel module the query for the view will take approximately 200ms to run when using the random sort criteria. However, when I turn off the random sort, it will run in approximately 80ms. This might not seem like a big deal, but when you have a large number of hits to the page with that query it could be.

I would recommend changing it from using the rand() function in SQL to pulling down the keys from that table, using PHP's rand function, then selecting those records. This would allow query caching to occur in MySQL. If a query using the rand() function, it can never be cached!

Anyone have any thoughts on how to get around this?

Thanks,
Shane

Comments

shanefjordan’s picture

shanefjordan commented

To use actually numbers, random query is 108.15ms whereas non-random query is .5ms. This shows the degrade in performance for a random query.

merlinofchaos’s picture

merlinofchaos commented

I cannot think of any situation in which doing a randomization in PHP, an interpreted language, would be faster than letting the database do it. Especially when it requires two queries to do.

shanefjordan’s picture

shanefjordan commented

Because you are having to pull down the entire table and then limit it to the top record. If you just pulled the keys, then you could get a straight shot at the random record(s) you would want. Wouldn't this also allow the first query to be cached by MySQL, since it would not change all of the time?

- Shane

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Closed (won't fix)

That still would not be faster, especially on large tables. The database is always more efficient than PHP.