aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
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
parent5bcfd02974389e1a131496d22f4321a79bed0fbb (diff)
downloadPeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.tar.gz
PeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.tar.zst
PeerTube-8cd72bd37724054f8942f2fefc7aa2e60eca74cf.zip
Optimize SQL queries
Diffstat (limited to 'server')
-rw-r--r--server/initializers/constants.ts2
-rw-r--r--server/initializers/migrations/0235-delete-some-video-indexes.ts41
-rw-r--r--server/models/account/account-video-rate.ts9
-rw-r--r--server/models/account/account.ts14
-rw-r--r--server/models/activitypub/actor.ts10
-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
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')
14 14
15// --------------------------------------------------------------------------- 15// ---------------------------------------------------------------------------
16 16
17const LAST_MIGRATION_VERSION = 230 17const LAST_MIGRATION_VERSION = 235
18 18
19// --------------------------------------------------------------------------- 19// ---------------------------------------------------------------------------
20 20
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 @@
1import * as Sequelize from 'sequelize'
2import { createClient } from 'redis'
3import { CONFIG } from '../constants'
4import { JobQueue } from '../../lib/job-queue'
5import { initDatabaseModels } from '../database'
6
7async function up (utils: {
8 transaction: Sequelize.Transaction
9 queryInterface: Sequelize.QueryInterface
10 sequelize: Sequelize.Sequelize
11}): Promise<any> {
12 await utils.sequelize.query('DROP INDEX IF EXISTS video_id_privacy_state_wait_transcoding;')
13 await utils.sequelize.query('DROP INDEX IF EXISTS video_name;')
14
15 for (let i = 0; i < 5; i++) {
16 const query = 'DELETE FROM "videoFile" WHERE id IN ' +
17 '(SELECT id FROM (SELECT MIN(id) AS id, "videoId", "resolution", "fps" ' +
18 'FROM "videoFile" GROUP BY "videoId", "resolution", "fps" HAVING COUNT(*) > 1) t)'
19 await utils.sequelize.query(query)
20 }
21
22 for (let i = 0; i < 5; i++) {
23 const query = 'DELETE FROM "actor" WHERE id IN ' +
24 '(SELECT id FROM (SELECT MIN(id) AS id, "uuid" ' +
25 'FROM "actor" GROUP BY "uuid" HAVING COUNT(*) > 1) t)'
26 await utils.sequelize.query(query)
27 }
28
29 for (let i = 0; i < 5; i++) {
30 const query = 'DELETE FROM "account" WHERE id IN ' +
31 '(SELECT id FROM (SELECT MIN(id) AS id, "actorId" ' +
32 'FROM "account" GROUP BY "actorId" HAVING COUNT(*) > 1) t)'
33 await utils.sequelize.query(query)
34 }
35}
36
37function down (options) {
38 throw new Error('Not implemented.')
39}
40
41export { 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'
17 { 17 {
18 fields: [ 'videoId', 'accountId' ], 18 fields: [ 'videoId', 'accountId' ],
19 unique: true 19 unique: true
20 },
21 {
22 fields: [ 'videoId' ]
23 },
24 {
25 fields: [ 'accountId' ]
26 },
27 {
28 fields: [ 'videoId', 'type' ]
20 } 29 }
21 ] 30 ]
22}) 31})
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'
46 ] 46 ]
47}) 47})
48@Table({ 48@Table({
49 tableName: 'account' 49 tableName: 'account',
50 indexes: [
51 {
52 fields: [ 'actorId' ],
53 unique: true
54 },
55 {
56 fields: [ 'applicationId' ]
57 },
58 {
59 fields: [ 'userId' ]
60 }
61 ]
50}) 62})
51export class AccountModel extends Model<AccountModel> { 63export class AccountModel extends Model<AccountModel> {
52 64
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 {
80 tableName: 'actor', 80 tableName: 'actor',
81 indexes: [ 81 indexes: [
82 { 82 {
83 fields: [ 'url' ] 83 fields: [ 'url' ],
84 unique: true
84 }, 85 },
85 { 86 {
86 fields: [ 'preferredUsername', 'serverId' ], 87 fields: [ 'preferredUsername', 'serverId' ],
@@ -94,6 +95,13 @@ enum ScopeNames {
94 }, 95 },
95 { 96 {
96 fields: [ 'avatarId' ] 97 fields: [ 'avatarId' ]
98 },
99 {
100 fields: [ 'uuid' ],
101 unique: true
102 },
103 {
104 fields: [ 'followersUrl' ]
97 } 105 }
98 ] 106 ]
99}) 107})
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 },