Post Ratings - taking likes to the next level [Deleted]

BamaStangGuy said:
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
Click to expand...


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')
 
Luke F said:
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')
Click to expand...

Thanks. Is it a default XF query without your addition? It doesn't seem to be pulling any other 3rd party data.
 
BamaStangGuy said:
Thanks. Is it a default XF query without your addition? It doesn't seem to be pulling any other 3rd party data.
Click to expand...

Yes it appears to be the query that is used when viewing individual forums, ie /forums/xxx

It is still present and still accesses a large number of rows with Post Ratings completely disabled
 
BamaStangGuy said:
Thanks. Is it a default XF query without your addition? It doesn't seem to be pulling any other 3rd party data.
Click to expand...
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.
 
Xon said:
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.
Click to expand...

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
 
How do i add boxes like this?
I can't seem to figure it out even though I tried.

3L4HAbh.png
 
Luke F said:
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
Click to expand...
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.
 
Xon said:
Damn, I'ld say start disabling stuff until it gets better. Might be something weird environmentally.
Click to expand...

I have a support ticket opened as well. It looks like it is a completely stock query and seems to only be slow due to the extras collected for moderators/admins.

As a guest the page loads very quickly.
 
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
 

Attachments

  • PR_ex_1.webp
    PR_ex_1.webp
    81.8 KB · Views: 7
  • PR_ex_2.webp
    PR_ex_2.webp
    73.5 KB · Views: 7
Linkzy said:
How do i add boxes like this?
I can't seem to figure it out even though I tried.

3L4HAbh.png
Click to expand...

That looks like it is from a custom theme, there is no built in feature that looks like that

mono said:
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
Click to expand...

Have you tried running recount ratings in the admin cp? I would recommend taking a backup first just in case
 
Mark T said:
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?
Click to expand...
Or maybe a snippet added to the query that pulls that data, to exclude banned users.
(I will look for the variable name)
 
This query is showing up in our slow query log:

Code: 
# 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;
 
Erictigerawr said:
@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.
Click to expand...
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.
 
Been a while, just starting back up. How would I go about redownloading the files? Starting up a community again and I lost my old download. Email might've been deleted.
 
Again i realize that the quote of a thread is calculated by the votes for the first post.
Lets see what is possible:

X writes a real nice article and 20 people say: I like it.
Now the thread has 20 thumbs up.

After the frist post we get 200 more posts from x, but people hate him for all the stupid things and vote 523 times with thumb up down in all the 200 posts after the first one.

Now we have still 20 thumbs up for the thread.


My solution:
1. Dont show the rating for the thread
2. Use another function/addon to rate the thread itself
3. A trigger for this addon to say: please calculate all votes for a value of the thread (every night a 4 by a cron-job)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom