aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-07-23 20:13:30 +0200
committerChocobozzz <me@florianbigard.com>2018-07-24 14:04:05 +0200
commit8cd72bd37724054f8942f2fefc7aa2e60eca74cf (patch)
treef2ed8da2a5a804286335156283659e9eaec5291a /server/models/video
parent5bcfd02974389e1a131496d22f4321a79bed0fbb (diff)
downloadPeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.tar.gz
PeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.tar.zst
PeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.zip
Optimize SQL queries
Diffstat (limited to 'server/models/video')
-rw-r--r--server/models/video/video-channel.ts3
-rw-r--r--server/models/video/video-comment.ts3
-rw-r--r--server/models/video/video-file.ts4
-rw-r--r--server/models/video/video.ts54
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 {
68 indexes: [ 68 indexes: [
69 { 69 {
70 fields: [ 'accountId' ] 70 fields: [ 'accountId' ]
71 },
72 {
73 fields: [ 'actorId' ]
71 } 74 }
72 ] 75 ]
73}) 76})
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 {
108 { 108 {
109 fields: [ 'url' ], 109 fields: [ 'url' ],
110 unique: true 110 unique: true
111 },
112 {
113 fields: [ 'accountId' ]
111 } 114 }
112 ] 115 ]
113}) 116})
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'
18 }, 18 },
19 { 19 {
20 fields: [ 'infoHash' ] 20 fields: [ 'infoHash' ]
21 },
22 {
23 fields: [ 'videoId', 'resolution', 'fps' ],
24 unique: true
21 } 25 }
22 ] 26 ]
23}) 27})
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'
99const indexes: Sequelize.DefineIndexesOptions[] = [ 99const indexes: Sequelize.DefineIndexesOptions[] = [
100 buildTrigramSearchIndex('video_name_trigram', 'name'), 100 buildTrigramSearchIndex('video_name_trigram', 'name'),
101 101
102 { fields: [ 'createdAt' ] },
103 { fields: [ 'publishedAt' ] },
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' ] },
113 { fields: [ 'likes' ] },
114 { fields: [ 'channelId' ] },
102 { 115 {
103 fields: [ 'createdAt' ] 116 fields: [ 'uuid' ],
104 }, 117 unique: true
105 {
106 fields: [ 'duration' ]
107 },
108 {
109 fields: [ 'views' ]
110 },
111 {
112 fields: [ 'likes' ]
113 },
114 {
115 fields: [ 'uuid' ]
116 },
117 {
118 fields: [ 'channelId' ]
119 },
120 {
121 fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ]
122 }, 118 },
123 { 119 {
124 fields: [ 'url'], 120 fields: [ 'url'],
@@ -212,16 +208,16 @@ type AvailableForListOptions = {
212 ), 208 ),
213 [ Sequelize.Op.in ]: Sequelize.literal( 209 [ Sequelize.Op.in ]: Sequelize.literal(
214 '(' + 210 '(' +
215 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + 211 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
216 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + 212 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
217 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 213 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
218 ' UNION ' + 214 ' UNION ' +
219 'SELECT "video"."id" AS "id" FROM "video" ' + 215 'SELECT "video"."id" AS "id" FROM "video" ' +
220 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 216 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
221 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 217 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
222 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 218 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
223 'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 219 'WHERE "actor"."serverId" IS NULL OR ' +
224 'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber + 220 '"actor"."id" IN (SELECT "targetActorId" FROM "actorFollow" WHERE "actorId" = 1)' + // Subquery for optimization
225 ')' 221 ')'
226 ) 222 )
227 }, 223 },