Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+2 votes
510 views
in Plugins by
Can this query be improved? This is being loaded in every page due to Best User widget and I suppose a join query is bad here especially when there are a large number of users.
"

SELECT ^userpoints.userid, ^userpoints.points - COALESCE(^userscores.points,0) AS mpoints
                                                                FROM `^userpoints`
                                                                LEFT JOIN `^userscores` on ^userpoints.userid=^userscores.userid
                                                                        AND YEAR(^userscores.date) = YEAR(CURDATE())
                                                                        AND MONTH(^userscores.date) = MONTH(CURDATE())
Q2A version: 1.7.0
by
I have fixed those performance issues in the premium version by using caching: http://www.q2apro.com/plugins/best-users-pro
by
Thanks :) I'll get the plugin soon.

1 Answer

+1 vote
by
edited by
I could find a way by modifying the query. The new query looks like as below.

Time for old query : ~250ms
New Query : ~50ms

But the real issue was old query using time functions and hence not getting cached. Time for new query with caching is ~0.1 ms :)

$suffix = " and ^userpoints.userid not in (select userid from ^users where flags & ".QA_USER_FLAGS_USER_BLOCKED." = 1)";
               

$queryRecentScores = qa_db_query_sub("SELECT ^userpoints.userid, ^userpoints.points - COALESCE(^userscores.points,0) AS mpoints
                                                                FROM `^userpoints`
                                                                LEFT JOIN `^userscores` on ^userpoints.userid=^userscores.userid
                                                                        AND DATE_FORMAT(^userscores.date,'%Y') like '".date("Y")."'
                                                                        AND DATE_FORMAT(^userscores.date,'%m') like '".date("m")."'  
                        
                                                                WHERE
                                                                ^userpoints.userid != ".$adminID.$suffix.
                                                                " ORDER BY mpoints DESC, ^userpoints.userid DESC limit ".$maxusers.";"
                                                                );

                                                                );

                // save all userscores in array $scores
                $scores = array();
                while ( ($row = qa_db_read_one_assoc($queryRecentScores,true)) !== null ) {
                        $scores[] = $row;
                }
...