1. 31 May, 2017 34 commits
  2. 30 May, 2017 6 commits
    • Douwe Maan's avatar
      04bb8fe9
    • Toon Claes's avatar
      Remove some deprecated methods · 1e5506d0
      Toon Claes authored
      To avoid the use of slow queries, remove some deprecated methods and encourage
      the use of ProjectFinder to find projects.
      1e5506d0
    • Toon Claes's avatar
      Add :owned param to ProjectFinder · db679788
      Toon Claes authored
      And use it in the API.
      db679788
    • Toon Claes's avatar
      Make it possible to combine :trending with other params · 5654ac87
      Toon Claes authored
      Now it is possible to combine the :non_public parameter. This might be useful
      when a user wants to know the trending projects they are member of.
      5654ac87
    • Toon Claes's avatar
      Use helper to construct Finder params · 0f0b9a84
      Toon Claes authored
      The ProjectsFinder and GroupFinder both support the same set of params. And the
      `/api/v4/projects` and `/api/v4/group/:id/projects` also support the same set of
      params. But they do not match the Finder params. So use a helper method to
      transform them.
      0f0b9a84
    • Toon Claes's avatar
      UNION of SELECT/WHERE is faster than WHERE on UNION · 01c6323d
      Toon Claes authored
      Instead of applying WHERE on a UNION, apply the WHERE on each of the seperate
      SELECT statements, and do UNION on that.
      
      Local tests with about 2_000_000 projects:
       - 1_500_000 private projects
       -    40_000 internal projects
       -   400_000 public projects
      
      For the API endpoint `/api/v4/projects?visibility=private` the slowest query was:
      
      ```sql
      SELECT "projects".*
      FROM "projects"
      WHERE ...
      ```
      
      The original query took 1073.8ms.
      The query refactored to UNION of SELECT/WHERE took 2.3ms.
      
      The original query was:
      
      ```sql
      SELECT "projects".*
      FROM "projects"
      WHERE "projects"."pending_delete" = $1
        AND (projects.id IN
               (SELECT "projects"."id"
                FROM "projects"
                INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                WHERE "projects"."pending_delete" = 'f'
                  AND "project_authorizations"."user_id" = 23
                UNION SELECT "projects"."id"
                FROM "projects"
                WHERE "projects"."visibility_level" IN (20,
                                                        10)))
        AND "projects"."visibility_level" = $2
        AND "projects"."archived" = $3
      ORDER BY "projects"."created_at" DESC
      LIMIT 20
      OFFSET 0 [["pending_delete", "f"],
             ["visibility_level", 0],
             ["archived", "f"]]
      ```
      
      The refactored query:
      ```sql
      SELECT "projects".*
      FROM "projects"
      WHERE "projects"."pending_delete" = $1
        AND (projects.id IN
               (SELECT "projects"."id"
                FROM "projects"
                INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                WHERE "projects"."pending_delete" = 'f'
                  AND "project_authorizations"."user_id" = 23
                  AND "projects"."visibility_level" = 0
                  AND "projects"."archived" = 'f'
                UNION SELECT "projects"."id"
                FROM "projects"
                WHERE "projects"."visibility_level" IN (20,
                                                        10)
                  AND "projects"."visibility_level" = 0
                  AND "projects"."archived" = 'f'))
      ORDER BY "projects"."created_at" DESC
      LIMIT 20
      OFFSET 0 [["pending_delete", "f"]]
      ```
      01c6323d