Continuing the discussion from Slow sql queries:
Clicking between topics is slow - for a few reasons - one of which is the generation of suggested topics.
For me I currently consider this a bug because suggested topics currently adds a ~640ms additional query delay to the initial display of a topic which is not critical to that topics display.
##What’s the impact?
For me with 40K topics and 430K posts on a 2GB Digital Ocean instance I’m seeing almost 500ms added to each topic load just from the “random” query for suggested topics.
Currently the function
TopicQuery::list_suggested_for generates a list of
random topics to augment the
new topics listed at the bottom of the each topic page.
random element of this uses
ORDER BY RANDOM in the SQL query, this is an easy but slow way of getting a random list of rows from the database.
Here is the function:
##The Suggestions Queries
As an example here are the numbers for a slightly faster than typical load - perhaps due to being cached.
###New Topic Suggestions
So new topic suggestions query is sorted as follows:
ORDER BY CASE WHEN topics.category_id = 79 THEN 0 ELSE 1 END, topics.bumped_at DESC LIMIT 5
This “new” query takes 120.1 ms
:straight_ruler: Screen capture of "new" query times (click to expand)
ORDER BY CASE WHEN topics.category_id = 79 THEN 0 ELSE 1 END, RANDOM() LIMIT 5
This “random” query takes 479.3 ms - this was a quite fast example.
:straight_ruler: Screen capture of "random" query times (click to expand)
##Solution / Alternative to
ORDER BY RANDOM
There are several alternatives to
ORDER BY RANDOM, one of which is this:
Linked from here: