Can this query be fixed so it doesn't take almost 7 seconds to load?
Code:SELECT thread.* , user.*, IF(user.username IS NULL, thread.username, user.username) AS username, deletion_log.delete_date, deletion_log.delete_reason, deletion_log.delete_user_id, deletion_log.delete_username, IF(thread_read.thread_read_date > 1483027983, thread_read.thread_read_date, 1483027983) AS thread_read_date, IF(thread_watch.user_id IS NULL, 0, IF(thread_watch.email_subscribe, 'watch_email', 'watch_no_email')) AS thread_is_watched, thread_user_post.post_count AS user_post_count, pr_cache.rating_cache FROM xf_thread AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) LEFT JOIN xf_deletion_log AS deletion_log ON (deletion_log.content_type = 'thread' AND deletion_log.content_id = thread.thread_id) LEFT JOIN xf_thread_read AS thread_read ON (thread_read.thread_id = thread.thread_id AND thread_read.user_id = 374904) LEFT JOIN xf_thread_watch AS thread_watch ON (thread_watch.thread_id = thread.thread_id AND thread_watch.user_id = 374904) LEFT JOIN xf_thread_user_post AS thread_user_post ON (thread_user_post.thread_id = thread.thread_id AND thread_user_post.user_id = 374904) left join dark_postrating_post_cache pr_cache ON (thread.first_post_id = pr_cache.post_id) WHERE (thread.node_id = 881) AND (thread.sticky = 0) AND (thread.discussion_state IN ('visible','deleted','moderated')) ORDER BY thread.last_post_date DESC
That query is not slow because of Post Ratings. If you remove everything added by post ratings it is still having to fetch and sort a large number of rows
(i.e. the lines containing 'pr_cache')
Thanks. Is it a default XF query without your addition? It doesn't seem to be pulling any other 3rd party data.
This is a semi-custom query by an add-on. XenForo queries should have some sort of limit clause to explicitly prevent pulling every non-sticky thread in the forum.Thanks. Is it a default XF query without your addition? It doesn't seem to be pulling any other 3rd party data.
This is a semi-custom query by an add-on. XenForo queries should have some sort of limit clause to explicitly prevent pulling every non-sticky thread in the forum.
I think it is a core query. One of the reasons this can get so slow, is that MySQL does early row lookup and effectively gathers every select data for every row examined.There was a line missing, it is 'limit 20' on mine (but still reporting a large number of rows in explain)
Though not sure if it is a core query or from another addon
I think it is a core query. One of the reasons this can get so slow, is that MySQL does early row lookup and effectively gathers every select data for every row examined.
@BamaStangGuy can you try this add-on? https://xenforo.com/community/resources/optimized-list-queries-by-xon.4487/
It implements some SQL rewriting to implement late row lookup which may help.
Damn, I'ld say start disabling stuff until it gets better. Might be something weird environmentally.No improvement with that add-on unfortunately.
Damn, I'ld say start disabling stuff until it gets better. Might be something weird environmentally.
How do i add boxes like this?
I can't seem to figure it out even though I tried.
How do i add boxes like this?
I can't seem to figure it out even though I tried.
Hi @Luke F. I have some issue with PR count. I guess it's connected with positive|neutral|negative ratings which have changed some time ago. It looks like some neutral ratings count like positive and otherwise. Could you help me to fix this?
Here're an examples
Yes, I did.Have you tried running recount ratings in the admin cp? I would recommend taking a backup first just in case
Or maybe a snippet added to the query that pulls that data, to exclude banned users.Has this ever been updated to not show banned members ?
Does anyone know where to add a conditional or something to filter out the banned members?
# Thread_id: 528 Schema: live_xf QC_hit: No
# Query_time: 1.415919 Lock_time: 0.000021 Rows_sent: 20 Rows_examined: 369488
# Rows_affected: 0
use live_xf;
SET timestamp=1485983221;
SELECT user.*
,
user_profile.*,
user_option.*,
user_privacy.*
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (1,2,4,5,6,7,8,9,10)) as positive_rating_count
, (user.like_count + coalesce((select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (1,2,4,5,6,7,8,$
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id) as total_rating_count
FROM xf_user AS user
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = user.user_id)
WHERE 1=1
ORDER BY positive_rating_count_incl_likes DESC
LIMIT 20;
Hey @Luke F any possible thought on this idea? Would be very handy for staff and moderators on sites against those who abuse this feature.@Luke F could you possibly look into adding a feature where we can remove a certain user's ratings that he has given in a certain time? I have some users who like to ratings boost and spams every post with a rating.
We use essential cookies to make this site work, and optional cookies to enhance your experience.