Optimize SQL queries

9 files changed, 108 insertions, 32 deletions

diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index 9f220aea5..e66ebb662 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -14,7 +14,7 @@ let config: IConfig = require('config')
 
 // ---------------------------------------------------------------------------
 
-const LAST_MIGRATION_VERSION = 230
+const LAST_MIGRATION_VERSION = 235
 
 // ---------------------------------------------------------------------------
 
diff --git a/server/initializers/migrations/0235-delete-some-video-indexes.ts b/server/initializers/migrations/0235-delete-some-video-indexes.ts
new file mode 100644
index 000000000..e362f240c
--- /dev/null
+++ b/server/initializers/migrations/0235-delete-some-video-indexes.ts
@@ -0,0 +1,41 @@
+import * as Sequelize from 'sequelize'
+import { createClient } from 'redis'
+import { CONFIG } from '../constants'
+import { JobQueue } from '../../lib/job-queue'
+import { initDatabaseModels } from '../database'
+
+async function up (utils: {
+  transaction: Sequelize.Transaction
+  queryInterface: Sequelize.QueryInterface
+  sequelize: Sequelize.Sequelize
+}): Promise<any> {
+  await utils.sequelize.query('DROP INDEX IF EXISTS video_id_privacy_state_wait_transcoding;')
+  await utils.sequelize.query('DROP INDEX IF EXISTS video_name;')
+
+  for (let i = 0; i < 5; i++) {
+    const query = 'DELETE FROM "videoFile" WHERE id IN ' +
+      '(SELECT id FROM (SELECT MIN(id) AS id, "videoId", "resolution", "fps" ' +
+      'FROM "videoFile" GROUP BY "videoId", "resolution", "fps" HAVING COUNT(*) > 1) t)'
+    await utils.sequelize.query(query)
+  }
+
+  for (let i = 0; i < 5; i++) {
+    const query = 'DELETE FROM "actor" WHERE id IN ' +
+      '(SELECT id FROM (SELECT MIN(id) AS id, "uuid" ' +
+      'FROM "actor" GROUP BY "uuid" HAVING COUNT(*) > 1) t)'
+    await utils.sequelize.query(query)
+  }
+
+  for (let i = 0; i < 5; i++) {
+    const query = 'DELETE FROM "account" WHERE id IN ' +
+      '(SELECT id FROM (SELECT MIN(id) AS id, "actorId" ' +
+      'FROM "account" GROUP BY "actorId" HAVING COUNT(*) > 1) t)'
+    await utils.sequelize.query(query)
+  }
+}
+
+function down (options) {
+  throw new Error('Not implemented.')
+}
+
+export { up, down }
diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts
index 9c19ec748..c99e32012 100644
--- a/server/models/account/account-video-rate.ts
+++ b/server/models/account/account-video-rate.ts
@@ -17,6 +17,15 @@ import { ActorModel } from '../activitypub/actor'
     {
       fields: [ 'videoId', 'accountId' ],
       unique: true
+    },
+    {
+      fields: [ 'videoId' ]
+    },
+    {
+      fields: [ 'accountId' ]
+    },
+    {
+      fields: [ 'videoId', 'type' ]
     }
   ]
 })
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 3ff59887d..2eed66fc2 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -46,7 +46,19 @@ import { UserModel } from './user'
   ]
 })
 @Table({
-  tableName: 'account'
+  tableName: 'account',
+  indexes: [
+    {
+      fields: [ 'actorId' ],
+      unique: true
+    },
+    {
+      fields: [ 'applicationId' ]
+    },
+    {
+      fields: [ 'userId' ]
+    }
+  ]
 })
 export class AccountModel extends Model<AccountModel> {
 
diff --git a/server/models/activitypub/actor.ts b/server/models/activitypub/actor.ts
index 38a689fea..267032e2a 100644
--- a/server/models/activitypub/actor.ts
+++ b/server/models/activitypub/actor.ts
@@ -80,7 +80,8 @@ enum ScopeNames {
   tableName: 'actor',
   indexes: [
     {
-      fields: [ 'url' ]
+      fields: [ 'url' ],
+      unique: true
     },
     {
       fields: [ 'preferredUsername', 'serverId' ],
@@ -94,6 +95,13 @@ enum ScopeNames {
     },
     {
       fields: [ 'avatarId' ]
+    },
+    {
+      fields: [ 'uuid' ],
+      unique: true
+    },
+    {
+      fields: [ 'followersUrl' ]
     }
   ]
 })
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
             ')'
           )
         },