Optimize SQL query that fetch actor outbox

4 files changed, 77 insertions, 27 deletions

diff --git a/server/lib/activitypub/actor.ts b/server/lib/activitypub/actor.ts
index a39b4e137..7494aadbb 100644
--- a/server/lib/activitypub/actor.ts
+++ b/server/lib/activitypub/actor.ts
@@ -212,7 +212,13 @@ function saveActorAndServerAndModelIfNotExist (
 
     // Force the actor creation, sometimes Sequelize skips the save() when it thinks the instance already exists
     // (which could be false in a retried query)
-    const actorCreated = await ActorModel.create(actor.toJSON(), { transaction: t })
+    const [ actorCreated ] = await ActorModel.findOrCreate({
+      defaults: actor.toJSON(),
+      where: {
+        url: actor.url
+      },
+      transaction: t
+    })
 
     if (actorCreated.type === 'Person' || actorCreated.type === 'Application') {
       const account = await saveAccount(actorCreated, result, t)
@@ -284,24 +290,36 @@ async function fetchRemoteActor (actorUrl: string): Promise<FetchRemoteActorResu
   }
 }
 
-function saveAccount (actor: ActorModel, result: FetchRemoteActorResult, t: Transaction) {
-  const account = new AccountModel({
-    name: result.name,
-    actorId: actor.id
+async function saveAccount (actor: ActorModel, result: FetchRemoteActorResult, t: Transaction) {
+  const [ accountCreated ] = await AccountModel.findOrCreate({
+    defaults: {
+      name: result.name,
+      actorId: actor.id
+    },
+    where: {
+      actorId: actor.id
+    },
+    transaction: t
   })
 
-  return account.save({ transaction: t })
+  return accountCreated
 }
 
 async function saveVideoChannel (actor: ActorModel, result: FetchRemoteActorResult, ownerActor: ActorModel, t: Transaction) {
-  const videoChannel = new VideoChannelModel({
-    name: result.name,
-    description: result.summary,
-    actorId: actor.id,
-    accountId: ownerActor.Account.id
+  const [ videoChannelCreated ] = await VideoChannelModel.findOrCreate({
+    defaults: {
+      name: result.name,
+      description: result.summary,
+      actorId: actor.id,
+      accountId: ownerActor.Account.id
+    },
+    where: {
+      actorId: actor.id
+    },
+    transaction: t
   })
 
-  return videoChannel.save({ transaction: t })
+  return videoChannelCreated
 }
 
 async function refreshActorIfNeeded (actor: ActorModel) {
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 0d115367f..7af68b20b 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -459,7 +459,8 @@ export class VideoModel extends Model<VideoModel> {
       },
       include: [
         {
-          model: VideoShareModel,
+          attributes: [ 'id' ],
+          model: VideoShareModel.unscoped(),
           required: false,
           where: {
             [Sequelize.Op.and]: [
@@ -475,28 +476,65 @@ export class VideoModel extends Model<VideoModel> {
           },
           include: [
             {
-              model: ActorModel,
-              required: true
+              attributes: [ 'id', 'url' ],
+              model: ActorModel.unscoped()
             }
           ]
         },
         {
-          model: VideoChannelModel,
+          model: VideoChannelModel.unscoped(),
           required: true,
           include: [
             {
-              model: AccountModel,
+              attributes: [ 'name' ],
+              model: AccountModel.unscoped(),
+              required: true,
+              include: [
+                {
+                  attributes: [ 'id', 'url' ],
+                  model: ActorModel.unscoped(),
+                  required: true
+                }
+              ]
+            },
+            {
+              attributes: [ 'id', 'url' ],
+              model: ActorModel.unscoped(),
               required: true
             }
           ]
         },
         {
+          attributes: [ 'type' ],
           model: AccountVideoRateModel,
-          include: [ AccountModel ]
+          required: false,
+          include: [
+            {
+              attributes: [ 'id' ],
+              model: AccountModel.unscoped(),
+              include: [
+                {
+                  attributes: [ 'url' ],
+                  model: ActorModel.unscoped(),
+                  include: [
+                    {
+                      attributes: [ 'host' ],
+                      model: ServerModel,
+                      required: false
+                    }
+                  ]
+                }
+              ]
+            }
+          ]
+        },
+        {
+          attributes: [ 'url' ],
+          model: VideoCommentModel,
+          required: false
         },
         VideoFileModel,
-        TagModel,
-        VideoCommentModel
+        TagModel
       ]
     }
 
diff --git a/server/tests/api/server/follows.ts b/server/tests/api/server/follows.ts
index c0115e534..b26af1a16 100644
--- a/server/tests/api/server/follows.ts
+++ b/server/tests/api/server/follows.ts
@@ -4,7 +4,7 @@ import * as chai from 'chai'
 import 'mocha'
 import { Video, VideoPrivacy } from '../../../../shared/models/videos'
 import { VideoComment, VideoCommentThreadTree } from '../../../../shared/models/videos/video-comment.model'
-import { checkVideoFilesWereRemoved, completeVideoCheck } from '../../utils'
+import { completeVideoCheck } from '../../utils'
 
 import {
   flushAndRunMultipleServers, flushTests, getVideosList, killallServers, ServerInfo, setAccessTokensToServers, uploadVideo,
@@ -353,8 +353,6 @@ describe('Test follows', function () {
 
       let res = await getVideosList(servers[ 0 ].url)
       expect(res.body.total).to.equal(1)
-
-      await checkVideoFilesWereRemoved(video4.uuid, servers[0].serverNumber)
     })
 
   })
diff --git a/server/tests/api/server/handle-down.ts b/server/tests/api/server/handle-down.ts
index 6ca8cfb64..e99e517e4 100644
--- a/server/tests/api/server/handle-down.ts
+++ b/server/tests/api/server/handle-down.ts
@@ -187,10 +187,6 @@ describe('Test handle downs', function () {
 
     await wait(5000)
 
-    const res = await getVideosList(servers[1].url)
-    expect(res.body.data).to.be.an('array')
-    expect(res.body.data).to.have.lengthOf(2)
-
     const resVideo = await getVideo(servers[1].url, videos[0].uuid)
     expect(resVideo.body).not.to.be.undefined