Best Users per Month

Question

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())

Comments

I have fixed those performance issues in the premium version by using caching: http://www.q2apro.com/plugins/best-users-pro

---

Thanks :) I'll get the plugin soon.

Answer 1

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;
                }