aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-12-20 15:25:24 +0100
committerChocobozzz <me@florianbigard.com>2018-12-20 15:25:49 +0100
commit439b1744f5f50b8530cded9398d51aa4bb5ed4ff (patch)
treec1e432dc55b377f2e7de44d8f6878ad161292b56
parent2f5c6b2fc6e60502c2a8df4dc9029c1d87ebe30b (diff)
downloadPeerTube-439b1744f5f50b8530cded9398d51aa4bb5ed4ff.tar.gz
PeerTube-439b1744f5f50b8530cded9398d51aa4bb5ed4ff.tar.zst
PeerTube-439b1744f5f50b8530cded9398d51aa4bb5ed4ff.zip
Optimize index sizes
-rw-r--r--server/initializers/constants.ts2
-rw-r--r--server/initializers/migrations/0310-drop-unused-video-indexes.ts32
-rw-r--r--server/models/video/video.ts44
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')
16 16
17// --------------------------------------------------------------------------- 17// ---------------------------------------------------------------------------
18 18
19const LAST_MIGRATION_VERSION = 305 19const LAST_MIGRATION_VERSION = 310
20 20
21// --------------------------------------------------------------------------- 21// ---------------------------------------------------------------------------
22 22
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 @@
1import * as Sequelize from 'sequelize'
2
3async function up (utils: {
4 transaction: Sequelize.Transaction,
5 queryInterface: Sequelize.QueryInterface,
6 sequelize: Sequelize.Sequelize,
7 db: any
8}): Promise<void> {
9 const indexNames = [
10 'video_category',
11 'video_licence',
12 'video_nsfw',
13 'video_language',
14 'video_wait_transcoding',
15 'video_state',
16 'video_remote',
17 'video_likes'
18 ]
19
20 for (const indexName of indexNames) {
21 await utils.sequelize.query('DROP INDEX IF EXISTS "' + indexName + '";')
22 }
23}
24
25function down (options) {
26 throw new Error('Not implemented.')
27}
28
29export {
30 up,
31 down
32}
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[] = [
102 { fields: [ 'createdAt' ] }, 102 { fields: [ 'createdAt' ] },
103 { fields: [ 'publishedAt' ] }, 103 { fields: [ 'publishedAt' ] },
104 { fields: [ 'duration' ] }, 104 { fields: [ 'duration' ] },
105 { fields: [ 'category' ] },
106 { fields: [ 'licence' ] },
107 { fields: [ 'nsfw' ] },
108 { fields: [ 'language' ] },
109 { fields: [ 'waitTranscoding' ] },
110 { fields: [ 'state' ] },
111 { fields: [ 'remote' ] },
112 { fields: [ 'views' ] }, 105 { fields: [ 'views' ] },
113 { fields: [ 'likes' ] },
114 { fields: [ 'channelId' ] }, 106 { fields: [ 'channelId' ] },
115 { 107 {
108 fields: [ 'category' ], // We don't care videos with an unknown category
109 where: {
110 category: {
111 [Sequelize.Op.ne]: null
112 }
113 }
114 },
115 {
116 fields: [ 'licence' ], // We don't care videos with an unknown licence
117 where: {
118 licence: {
119 [Sequelize.Op.ne]: null
120 }
121 }
122 },
123 {
124 fields: [ 'language' ], // We don't care videos with an unknown language
125 where: {
126 language: {
127 [Sequelize.Op.ne]: null
128 }
129 }
130 },
131 {
132 fields: [ 'nsfw' ], // Most of the videos are not NSFW
133 where: {
134 nsfw: true
135 }
136 },
137 {
138 fields: [ 'remote' ], // Only index local videos
139 where: {
140 remote: false
141 }
142 },
143 {
116 fields: [ 'uuid' ], 144 fields: [ 'uuid' ],
117 unique: true 145 unique: true
118 }, 146 },