From 6bba3ace19977ef2c04241df2dacbdccd295f49b Mon Sep 17 00:00:00 2001
From: Simon Brandhof <simon.brandhof@sonarsource.com>
Date: Mon, 7 Mar 2016 13:44:05 +0100
Subject: [PATCH] SONAR-7129 SONAR-7135 optimize SQL requests on MySQL

---
 .../sonar/db/component/ComponentMapper.xml    | 36 +++++++++++--------
 .../sonar/db/component/ComponentDaoTest.java  | 25 +++++++++++--
 2 files changed, 44 insertions(+), 17 deletions(-)

diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
index ebfb03f5e33..8dc2a0e550c 100644
--- a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
@@ -289,17 +289,18 @@
       </if>
       <if test="query.nameOrKeyQuery!=null">
         AND (
-          exists (
-            select 1
-            from resource_index ri
-            where
-            ri.resource_id=p.id
+          p.kee=#{query.nameOrKeyQuery}
+          OR
+          p.id IN (
+            SELECT ri.resource_id
+            FROM resource_index ri
+            WHERE ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/'
             AND ri.qualifier in
             <foreach collection="query.qualifiers" item="qualifier" open="(" close=")" separator=",">
               #{qualifier}
             </foreach>
-            AND ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/')
-          OR p.kee = #{query.nameOrKeyQuery})
+          )
+        )
       </if>
     </where>
   </sql>
@@ -354,13 +355,20 @@
     </if>
     <if test="query.nameOrKeyQuery!=null">
       AND (
-        exists (
-          select 1
-          from resource_index ri
-          where
-          (ri.resource_id=p.id OR ri.resource_id=p.copy_resource_id)
-            AND ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/')
-        OR p.kee = #{query.nameOrKeyQuery})
+        p.kee=#{query.nameOrKeyQuery}
+        OR
+        p.id IN (
+          SELECT ri.resource_id
+          FROM resource_index ri
+          WHERE ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/'
+        )
+        OR
+        p.copy_resource_id IN (
+          SELECT ri.resource_id
+          FROM resource_index ri
+          WHERE ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/'
+        )
+      )
     </if>
   </sql>
 
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
index e739cba9096..908337ffba1 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
@@ -776,8 +776,8 @@ public class ComponentDaoTest {
     ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
     SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
     SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
-    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid").setKey("file-key-1"), projectSnapshot);
-    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid").setKey("file-key-2"), moduleSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid").setKey("file-key-1").setName("File one"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid").setKey("file-key-2").setName("File two"), moduleSnapshot);
     db.commit();
     componentDb.indexProjects();
 
@@ -871,7 +871,7 @@ public class ComponentDaoTest {
   }
 
   @Test
-  public void select_direct_children_of_a_view() {
+  public void list_direct_children_of_a_view() {
     ComponentDto view = newView("view-uuid");
     SnapshotDto viewSnapshot = componentDb.insertViewAndSnapshot(view);
     // one subview
@@ -882,6 +882,25 @@ public class ComponentDaoTest {
     componentDb.insertProjectAndSnapshot(project);
     componentDb.insertComponentAndSnapshot(newProjectCopy("project-copy-uuid", project, view), viewSnapshot);
     componentDb.indexProjects();
+    ComponentTreeQuery dbQuery = newTreeQuery(viewSnapshot).build();
+
+    List<ComponentDtoWithSnapshotId> components = underTest.selectDirectChildren(dbSession, dbQuery);
+
+    assertThat(components).extracting("uuid").containsOnly("project-copy-uuid", "subview-uuid");
+  }
+
+  @Test
+  public void search_direct_children_of_a_view() {
+    ComponentDto view = newView("view-uuid");
+    SnapshotDto viewSnapshot = componentDb.insertViewAndSnapshot(view);
+    // one subview
+    ComponentDto subView = newSubView(view, "subview-uuid", "subview-key").setName("subview name");
+    componentDb.insertComponentAndSnapshot(subView, viewSnapshot);
+    // one project and its copy linked to the view
+    ComponentDto project = newProjectDto("project-uuid").setName("project name");
+    componentDb.insertProjectAndSnapshot(project);
+    componentDb.insertComponentAndSnapshot(newProjectCopy("project-copy-uuid", project, view), viewSnapshot);
+    componentDb.indexProjects();
     ComponentTreeQuery dbQuery = newTreeQuery(viewSnapshot).setNameOrKeyQuery("name").build();
 
     List<ComponentDtoWithSnapshotId> components = underTest.selectDirectChildren(dbSession, dbQuery);
-- 
2.39.5