From 8cd72bd37724054f8942f2fefc7aa2e60eca74cf Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Mon, 23 Jul 2018 20:13:30 +0200 Subject: [PATCH] Optimize SQL queries --- client/src/app/search/search.component.html | 4 +- server/initializers/constants.ts | 2 +- .../0235-delete-some-video-indexes.ts | 41 ++++++++++++++ server/models/account/account-video-rate.ts | 9 ++++ server/models/account/account.ts | 14 ++++- server/models/activitypub/actor.ts | 10 +++- server/models/video/video-channel.ts | 3 ++ server/models/video/video-comment.ts | 3 ++ server/models/video/video-file.ts | 4 ++ server/models/video/video.ts | 54 +++++++++---------- 10 files changed, 110 insertions(+), 34 deletions(-) create mode 100644 server/initializers/migrations/0235-delete-some-video-indexes.ts diff --git a/client/src/app/search/search.component.html b/client/src/app/search/search.component.html index 3a63dbcec..a0b5e6e79 100644 --- a/client/src/app/search/search.component.html +++ b/client/src/app/search/search.component.html @@ -9,7 +9,7 @@
Filters @@ -17,7 +17,7 @@
- +
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 { + 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 { 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 ')' ) }, -- 2.25.1