From dbfd3e9bfef86bf935a24c9a325d9f06e3978b8f Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 27 Jul 2018 15:20:10 +0200 Subject: Optimize search SQL query (I hope :p) --- server/models/utils.ts | 14 +------------- server/models/video/video.ts | 32 ++++++++++++++++---------------- 2 files changed, 17 insertions(+), 29 deletions(-) diff --git a/server/models/utils.ts b/server/models/utils.ts index 99e146583..58a18c97a 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -51,17 +51,6 @@ function createSimilarityAttribute (col: string, value: string) { ) } -function createSearchTrigramQuery (col: string, value: string) { - return { - [ Sequelize.Op.or ]: [ - // FIXME: use word_similarity instead of just similarity? - Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)), - - Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`)) - ] - } -} - // --------------------------------------------------------------------------- export { @@ -69,8 +58,7 @@ export { getSortOnModel, createSimilarityAttribute, throwIfNotValid, - buildTrigramSearchIndex, - createSearchTrigramQuery + buildTrigramSearchIndex } // --------------------------------------------------------------------------- diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 33d62926b..464dbf597 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -83,7 +83,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' +import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -883,24 +883,24 @@ export class VideoModel extends Model { } const attributesInclude = [] + const escapedSearch = VideoModel.sequelize.escape(options.search) + const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%') if (options.search) { whereAnd.push( { - [ Sequelize.Op.or ]: [ - createSearchTrigramQuery('VideoModel.name', options.search), - - { - id: { - [ Sequelize.Op.in ]: Sequelize.literal( - '(' + - 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' + - 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + - 'WHERE "tag"."name" = ' + VideoModel.sequelize.escape(options.search) + - ')' - ) - } - } - ] + id: { + [ Sequelize.Op.in ]: Sequelize.literal( + '(' + + 'SELECT "video"."id" FROM "video" WHERE ' + + 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + + 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + + 'UNION ALL ' + + 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' + + 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + + 'WHERE "tag"."name" = ' + escapedSearch + + ')' + ) + } } ) -- cgit v1.2.3