[GitHub] [hadoop] slfan1989 commented on a diff in pull request #4846: YARN-11290. Improve Query Condition of FederationStateStore#getApplicationsHomeSubCluster.

Tue, 13 Sep 2022 12:28:13 -0700 


slfan1989 commented on code in PR #4846:
URL: https://github.com/apache/hadoop/pull/4846#discussion_r970008365


##########
hadoop-yarn-project/hadoop-yarn/bin/FederationStateStore/MySQL/FederationStateStoreStoredProcs.sql:
##########
@@ -122,10 +122,26 @@ BEGIN
    WHERE applicationId = applicationID_IN;
 END //
 
-CREATE PROCEDURE sp_getApplicationsHomeSubCluster()
-BEGIN
-   SELECT applicationId, homeSubCluster
-   FROM applicationsHomeSubCluster;
+CREATE PROCEDURE sp_getApplicationsHomeSubCluster(IN limit_IN int, IN 
homeSubCluster_IN varchar(256))
+BEGIN
+   SELECT
+       applicationId,
+       homeSubCluster,
+       createTime
+   FROM
+    (SELECT
+       applicationId,
+       homeSubCluster,
+       createTime,

Review Comment:
   We can sort internally and get the sorted result directly externally.
   
   ```
   CREATE PROCEDURE sp_getApplicationsHomeSubCluster(IN limit_IN int, IN 
homeSubCluster_IN varchar(256))
   BEGIN
      SELECT
          applicationId,
          homeSubCluster,
          createTime
        -- In this part, we can get the results directly, which are already 
sorted
       FROM
           (SELECT
                *,
                @rownum := 0,
                IF(homeSubCluster_IN = '', 1, (homeSubCluster = 
homeSubCluster_IN)) AS filter_result
            FROM applicationshomesubcluster
            -- In this part we have sorted
            ORDER BY createTime DESC) AS app_home_sc
       WHERE filter_result = 1
         AND (@rownum := @rownum + 1) <= limit_IN;
   END //
   ```
   Case1: parameters  `limit_IN = 10 homeSubCluster_IN = ''`, We expect to get 
10 records in reverse order.
   <img width="724" alt="image" 
src="https://user-images.githubusercontent.com/55643692/189991492-dbb3d63f-578a-4b58-ad06-0bb52074cd0c.png";>
   
   Case2: parameters  `limit_IN = 10 homeSubCluster_IN = 'SC-1'`, We expect to 
get 5 records in reverse order.
   <img width="629" alt="image" 
src="https://user-images.githubusercontent.com/55643692/189992078-5b056737-8e91-48a1-bce1-597f757877ab.png";>
   
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to