Optimize index sizes

3 files changed, 69 insertions, 9 deletions

diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index 1c27a9f6b..c3df2383a 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -16,7 +16,7 @@ let config: IConfig = require('config')
 
 // ---------------------------------------------------------------------------
 
-const LAST_MIGRATION_VERSION = 305
+const LAST_MIGRATION_VERSION = 310
 
 // ---------------------------------------------------------------------------
 
diff --git a/server/initializers/migrations/0310-drop-unused-video-indexes.ts b/server/initializers/migrations/0310-drop-unused-video-indexes.ts
new file mode 100644
index 000000000..d51f430c0
--- /dev/null
+++ b/server/initializers/migrations/0310-drop-unused-video-indexes.ts
@@ -0,0 +1,32 @@
+import * as Sequelize from 'sequelize'
+
+async function up (utils: {
+  transaction: Sequelize.Transaction,
+  queryInterface: Sequelize.QueryInterface,
+  sequelize: Sequelize.Sequelize,
+  db: any
+}): Promise<void> {
+  const indexNames = [
+    'video_category',
+    'video_licence',
+    'video_nsfw',
+    'video_language',
+    'video_wait_transcoding',
+    'video_state',
+    'video_remote',
+    'video_likes'
+  ]
+
+  for (const indexName of indexNames) {
+    await utils.sequelize.query('DROP INDEX IF EXISTS "' + indexName + '";')
+  }
+}
+
+function down (options) {
+  throw new Error('Not implemented.')
+}
+
+export {
+  up,
+  down
+}
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 3f282580c..bcf327f32 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -102,17 +102,45 @@ const indexes: Sequelize.DefineIndexesOptions[] = [
   { 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: [ 'category' ], // We don't care videos with an unknown category
+    where: {
+      category: {
+        [Sequelize.Op.ne]: null
+      }
+    }
+  },
+  {
+    fields: [ 'licence' ], // We don't care videos with an unknown licence
+    where: {
+      licence: {
+        [Sequelize.Op.ne]: null
+      }
+    }
+  },
+  {
+    fields: [ 'language' ], // We don't care videos with an unknown language
+    where: {
+      language: {
+        [Sequelize.Op.ne]: null
+      }
+    }
+  },
+  {
+    fields: [ 'nsfw' ], // Most of the videos are not NSFW
+    where: {
+      nsfw: true
+    }
+  },
+  {
+    fields: [ 'remote' ], // Only index local videos
+    where: {
+      remote: false
+    }
+  },
+  {
     fields: [ 'uuid' ],
     unique: true
   },