Optimize SQL queries

4 files changed, 35 insertions, 29 deletions

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 4251afce9..6567b00d6 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -68,6 +68,9 @@ enum ScopeNames {
   indexes: [
     {
       fields: [ 'accountId' ]
+    },
+    {
+      fields: [ 'actorId' ]
     }
   ]
 })
diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index f93d81d67..e79aff209 100644
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -108,6 +108,9 @@ enum ScopeNames {
     {
       fields: [ 'url' ],
       unique: true
+    },
+    {
+      fields: [ 'accountId' ]
     }
   ]
 })
diff --git a/server/models/video/video-file.ts b/server/models/video/video-file.ts
index 372d18d69..f5a2b6c1f 100644
--- a/server/models/video/video-file.ts
+++ b/server/models/video/video-file.ts
@@ -18,6 +18,10 @@ import { VideoModel } from './video'
     },
     {
       fields: [ 'infoHash' ]
+    },
+    {
+      fields: [ 'videoId', 'resolution', 'fps' ],
+      unique: true
     }
   ]
 })
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index b97dfd96f..27e73bbf1 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -99,26 +99,22 @@ import { VideosSearchQuery } from '../../../shared/models/search'
 const indexes: Sequelize.DefineIndexesOptions[] = [
   buildTrigramSearchIndex('video_name_trigram', 'name'),
 
+  { fields: [ 'createdAt' ] },
+  { fields: [ 'publishedAt' ] },
+  { fields: [ 'duration' ] },
+  { fields: [ 'category' ] },
+  { fields: [ 'licence' ] },
+  { fields: [ 'nsfw' ] },
+  { fields: [ 'language' ] },
+  { fields: [ 'waitTranscoding' ] },
+  { fields: [ 'state' ] },
+  { fields: [ 'remote' ] },
+  { fields: [ 'views' ] },
+  { fields: [ 'likes' ] },
+  { fields: [ 'channelId' ] },
   {
-    fields: [ 'createdAt' ]
-  },
-  {
-    fields: [ 'duration' ]
-  },
-  {
-    fields: [ 'views' ]
-  },
-  {
-    fields: [ 'likes' ]
-  },
-  {
-    fields: [ 'uuid' ]
-  },
-  {
-    fields: [ 'channelId' ]
-  },
-  {
-    fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ]
+    fields: [ 'uuid' ],
+    unique: true
   },
   {
     fields: [ 'url'],
@@ -212,16 +208,16 @@ type AvailableForListOptions = {
           ),
           [ Sequelize.Op.in ]: Sequelize.literal(
             '(' +
-            'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
-            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-            ' UNION ' +
-            'SELECT "video"."id" AS "id" FROM "video" ' +
-            'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-            'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
-            'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
-            'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
-            'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber +
+              'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
+              'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
+              'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+              ' UNION ' +
+              'SELECT "video"."id" AS "id" FROM "video" ' +
+              'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+              'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+              'WHERE "actor"."serverId" IS NULL OR ' +
+              '"actor"."id" IN (SELECT "targetActorId" FROM "actorFollow" WHERE "actorId" = 1)' + // Subquery for optimization
             ')'
           )
         },