From 8cd72bd37724054f8942f2fefc7aa2e60eca74cf Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Mon, 23 Jul 2018 20:13:30 +0200 Subject: Optimize SQL queries --- server/initializers/constants.ts | 2 +- .../migrations/0235-delete-some-video-indexes.ts | 41 ++++++++++++++++++++++ 2 files changed, 42 insertions(+), 1 deletion(-) create mode 100644 server/initializers/migrations/0235-delete-some-video-indexes.ts (limited to 'server/initializers') 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 } -- cgit v1.2.3