[MediaWiki-commits] [Gerrit] Improve performance of phase2 report query - change (wikimedia/wikimania-scholarships)

[jenkins-bot (Code Review)]

jenkins-bot has submitted this change and it was merged.

Change subject: Improve performance of phase2 report query
......................................................................


Improve performance of phase2 report query

Replace the horrible INNER JOIN based filtering with more performant
HAVING clause conditions.

Bug: T94373
Change-Id: I9fcc3f251d56f16584b9c6f1bfca5b25f6f3e5a6
---
M src/Wikimania/Scholarship/Dao/AbstractDao.php
M src/Wikimania/Scholarship/Dao/Apply.php
2 files changed, 26 insertions(+), 13 deletions(-)

Approvals:
  BryanDavis: Looks good to me, approved
  Niharika29: Looks good to me, but someone else must approve
  jenkins-bot: Verified



diff --git a/src/Wikimania/Scholarship/Dao/AbstractDao.php 
b/src/Wikimania/Scholarship/Dao/AbstractDao.php
index 36bff32..ff74e90 100644
--- a/src/Wikimania/Scholarship/Dao/AbstractDao.php
+++ b/src/Wikimania/Scholarship/Dao/AbstractDao.php
@@ -233,6 +233,20 @@
                return '';
        }
 
+       /**
+        * Construct a having clause.
+        * @param array $where List of conditions
+        * @param string $conjunction Joining operation ('and' or 'or')
+        * @return string Having clause or empty string
+        */
+       protected static function buildHaving(
+               array $having, $conjunction = 'AND'
+       ) {
+               if ( $having ) {
+                       return 'HAVING (' . implode( ") {$conjunction} (", 
$having ) . ') ';
+               }
+               return '';
+       }
 
        /**
         * Create a string by joining all arguments with spaces.
diff --git a/src/Wikimania/Scholarship/Dao/Apply.php 
b/src/Wikimania/Scholarship/Dao/Apply.php
index f7b51cb..1b9c25c 100644
--- a/src/Wikimania/Scholarship/Dao/Apply.php
+++ b/src/Wikimania/Scholarship/Dao/Apply.php
@@ -563,6 +563,11 @@
                        "COALESCE(:expshare * rkexps.expshare, 0)) as p2score ",
                );
 
+               $having = array(
+                       'p1score >= :int_phase1pass',
+                       's.exclude = 0',
+               );
+
                $params['relexp'] = (float)$this->settings['relexp'];
                $params['expshare'] = (float)$this->settings['expshare'];
 
@@ -580,23 +585,20 @@
 
                if ( $region != 'All' ) {
                        $params['region'] = $region;
-                       $regionJoin = 'INNER JOIN iso_countries c1 ON c.region 
= :region';
-               } else {
-                       $regionJoin = '';
+                       $fields[] = 'c.region';
+                       $having[] = 'c.region = :region';
                }
 
                if ( $globalns != 'All' ) {
                        $params['globalns'] = $globalns;
-                       $globalnsJoin = 'INNER JOIN iso_countries c2 ON 
c.globalns = :globalns';
-               } else {
-                       $globalnsJoin = '';
+                       $fields[] = 'c.globalns';
+                       $having[] = 'c.globalns = :globalns';
                }
 
                if ( $languageGroup != 'All' ) {
                        $params['languageGroup'] = $languageGroup;
-                       $languageGroupJoin = 'INNER JOIN language_communities 
l1 ON l.size = :languageGroup';
-               } else {
-                       $languageGroupJoin = '';
+                       $fields[] = 'l.size';
+                       $having[] = 'l.size = :languageGroup';
                }
 
                $sql = self::concat(
@@ -608,11 +610,8 @@
                        "LEFT OUTER JOIN ({$sqlNumScorers}) ns ON s.id = 
ns.scholarship_id",
                        "LEFT OUTER JOIN iso_countries c ON s.residence = 
c.code",
                        "LEFT OUTER JOIN language_communities l ON s.community 
= l.code",
-                       $regionJoin,
-                       $globalnsJoin,
-                       $languageGroupJoin,
                        'GROUP BY s.id, s.fname, s.lname, s.email, s.residence',
-                       'HAVING p1score >= :int_phase1pass AND s.exclude = 0',
+                       self::buildHaving( $having ),
                        'ORDER BY p2score DESC, s.id ASC'
                );
 

-- 
To view, visit https://gerrit.wikimedia.org/r/201070
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings

Gerrit-MessageType: merged
Gerrit-Change-Id: I9fcc3f251d56f16584b9c6f1bfca5b25f6f3e5a6
Gerrit-PatchSet: 2
Gerrit-Project: wikimedia/wikimania-scholarships
Gerrit-Branch: master
Gerrit-Owner: BryanDavis <bda...@wikimedia.org>
Gerrit-Reviewer: Aude <aude.w...@gmail.com>
Gerrit-Reviewer: BryanDavis <bda...@wikimedia.org>
Gerrit-Reviewer: Chad <ch...@wikimedia.org>
Gerrit-Reviewer: Niharika29 <niharikakohl...@gmail.com>
Gerrit-Reviewer: jenkins-bot <>

_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits