Optimize default sort when listing videos

createdAt does not have the appropriate index

7 files changed, 79 insertions, 73 deletions

diff --git a/server/controllers/api/accounts.ts b/server/controllers/api/accounts.ts
index b1c05c6c0..0a73dfcbf 100644
--- a/server/controllers/api/accounts.ts
+++ b/server/controllers/api/accounts.ts
@@ -1,5 +1,8 @@
 import * as express from 'express'
+import { getServerActor } from '@server/models/application/application'
+import { buildNSFWFilter, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils'
 import { getFormattedObjects } from '../../helpers/utils'
+import { JobQueue } from '../../lib/job-queue'
 import {
   asyncMiddleware,
   authenticate,
@@ -8,6 +11,7 @@ import {
   paginationValidator,
   setDefaultPagination,
   setDefaultSort,
+  setDefaultVideosSort,
   videoPlaylistsSortValidator,
   videoRatesSortValidator,
   videoRatingValidator
@@ -17,18 +21,15 @@ import {
   accountsSortValidator,
   ensureAuthUserOwnsAccountValidator,
   videoChannelsSortValidator,
-  videosSortValidator,
-  videoChannelStatsValidator
+  videoChannelStatsValidator,
+  videosSortValidator
 } from '../../middlewares/validators'
+import { commonVideoPlaylistFiltersValidator, videoPlaylistsSearchValidator } from '../../middlewares/validators/videos/video-playlists'
 import { AccountModel } from '../../models/account/account'
 import { AccountVideoRateModel } from '../../models/account/account-video-rate'
 import { VideoModel } from '../../models/video/video'
-import { buildNSFWFilter, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils'
 import { VideoChannelModel } from '../../models/video/video-channel'
-import { JobQueue } from '../../lib/job-queue'
 import { VideoPlaylistModel } from '../../models/video/video-playlist'
-import { commonVideoPlaylistFiltersValidator, videoPlaylistsSearchValidator } from '../../middlewares/validators/videos/video-playlists'
-import { getServerActor } from '@server/models/application/application'
 
 const accountsRouter = express.Router()
 
@@ -49,7 +50,7 @@ accountsRouter.get('/:accountName/videos',
   asyncMiddleware(accountNameWithHostGetValidator),
   paginationValidator,
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   setDefaultPagination,
   optionalAuthenticate,
   commonVideosFiltersValidator,
diff --git a/server/controllers/api/users/me.ts b/server/controllers/api/users/me.ts
index 914c52e27..ba60a3d2a 100644
--- a/server/controllers/api/users/me.ts
+++ b/server/controllers/api/users/me.ts
@@ -17,6 +17,7 @@ import {
   paginationValidator,
   setDefaultPagination,
   setDefaultSort,
+  setDefaultVideosSort,
   usersUpdateMeValidator,
   usersVideoRatingValidator
 } from '../../../middlewares'
@@ -60,7 +61,7 @@ meRouter.get('/me/videos',
   authenticate,
   paginationValidator,
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   setDefaultPagination,
   asyncMiddleware(getUserVideos)
 )
diff --git a/server/controllers/api/users/my-subscriptions.ts b/server/controllers/api/users/my-subscriptions.ts
index 8b88feaf3..b8c234eef 100644
--- a/server/controllers/api/users/my-subscriptions.ts
+++ b/server/controllers/api/users/my-subscriptions.ts
@@ -1,7 +1,11 @@
-import * as express from 'express'
 import 'multer'
+import * as express from 'express'
+import { VideoFilter } from '../../../../shared/models/videos/video-query.type'
+import { buildNSFWFilter, getCountVideos } from '../../../helpers/express-utils'
 import { getFormattedObjects } from '../../../helpers/utils'
 import { WEBSERVER } from '../../../initializers/constants'
+import { sequelizeTypescript } from '../../../initializers/database'
+import { JobQueue } from '../../../lib/job-queue'
 import {
   asyncMiddleware,
   asyncRetryTransactionMiddleware,
@@ -10,21 +14,18 @@ import {
   paginationValidator,
   setDefaultPagination,
   setDefaultSort,
+  setDefaultVideosSort,
   userSubscriptionAddValidator,
   userSubscriptionGetValidator
 } from '../../../middlewares'
 import {
   areSubscriptionsExistValidator,
+  userSubscriptionListValidator,
   userSubscriptionsSortValidator,
-  videosSortValidator,
-  userSubscriptionListValidator
+  videosSortValidator
 } from '../../../middlewares/validators'
-import { VideoModel } from '../../../models/video/video'
-import { buildNSFWFilter, getCountVideos } from '../../../helpers/express-utils'
-import { VideoFilter } from '../../../../shared/models/videos/video-query.type'
 import { ActorFollowModel } from '../../../models/activitypub/actor-follow'
-import { JobQueue } from '../../../lib/job-queue'
-import { sequelizeTypescript } from '../../../initializers/database'
+import { VideoModel } from '../../../models/video/video'
 
 const mySubscriptionsRouter = express.Router()
 
@@ -32,7 +33,7 @@ mySubscriptionsRouter.get('/me/subscriptions/videos',
   authenticate,
   paginationValidator,
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   setDefaultPagination,
   commonVideosFiltersValidator,
   asyncMiddleware(getUserSubscriptionVideos)
diff --git a/server/controllers/api/video-channel.ts b/server/controllers/api/video-channel.ts
index f705034fd..45c936978 100644
--- a/server/controllers/api/video-channel.ts
+++ b/server/controllers/api/video-channel.ts
@@ -1,5 +1,20 @@
 import * as express from 'express'
+import { getServerActor } from '@server/models/application/application'
+import { MChannelAccountDefault } from '@server/types/models'
+import { VideoChannelCreate, VideoChannelUpdate } from '../../../shared'
+import { auditLoggerFactory, getAuditIdFromRes, VideoChannelAuditView } from '../../helpers/audit-logger'
+import { resetSequelizeInstance } from '../../helpers/database-utils'
+import { buildNSFWFilter, createReqFiles, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils'
+import { logger } from '../../helpers/logger'
 import { getFormattedObjects } from '../../helpers/utils'
+import { CONFIG } from '../../initializers/config'
+import { MIMETYPES } from '../../initializers/constants'
+import { sequelizeTypescript } from '../../initializers/database'
+import { setAsyncActorKeys } from '../../lib/activitypub/actor'
+import { sendUpdateActor } from '../../lib/activitypub/send'
+import { updateActorAvatarFile } from '../../lib/avatar'
+import { JobQueue } from '../../lib/job-queue'
+import { createLocalVideoChannel, federateAllVideosOfChannel } from '../../lib/video-channel'
 import {
   asyncMiddleware,
   asyncRetryTransactionMiddleware,
@@ -9,34 +24,20 @@ import {
   paginationValidator,
   setDefaultPagination,
   setDefaultSort,
+  setDefaultVideosSort,
   videoChannelsAddValidator,
   videoChannelsRemoveValidator,
   videoChannelsSortValidator,
   videoChannelsUpdateValidator,
   videoPlaylistsSortValidator
 } from '../../middlewares'
-import { VideoChannelModel } from '../../models/video/video-channel'
-import { videoChannelsNameWithHostValidator, videosSortValidator, videoChannelsOwnSearchValidator } from '../../middlewares/validators'
-import { sendUpdateActor } from '../../lib/activitypub/send'
-import { VideoChannelCreate, VideoChannelUpdate } from '../../../shared'
-import { createLocalVideoChannel, federateAllVideosOfChannel } from '../../lib/video-channel'
-import { buildNSFWFilter, createReqFiles, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils'
-import { setAsyncActorKeys } from '../../lib/activitypub/actor'
+import { videoChannelsNameWithHostValidator, videoChannelsOwnSearchValidator, videosSortValidator } from '../../middlewares/validators'
+import { updateAvatarValidator } from '../../middlewares/validators/avatar'
+import { commonVideoPlaylistFiltersValidator } from '../../middlewares/validators/videos/video-playlists'
 import { AccountModel } from '../../models/account/account'
-import { MIMETYPES } from '../../initializers/constants'
-import { logger } from '../../helpers/logger'
 import { VideoModel } from '../../models/video/video'
-import { updateAvatarValidator } from '../../middlewares/validators/avatar'
-import { updateActorAvatarFile } from '../../lib/avatar'
-import { auditLoggerFactory, getAuditIdFromRes, VideoChannelAuditView } from '../../helpers/audit-logger'
-import { resetSequelizeInstance } from '../../helpers/database-utils'
-import { JobQueue } from '../../lib/job-queue'
+import { VideoChannelModel } from '../../models/video/video-channel'
 import { VideoPlaylistModel } from '../../models/video/video-playlist'
-import { commonVideoPlaylistFiltersValidator } from '../../middlewares/validators/videos/video-playlists'
-import { CONFIG } from '../../initializers/config'
-import { sequelizeTypescript } from '../../initializers/database'
-import { MChannelAccountDefault } from '@server/types/models'
-import { getServerActor } from '@server/models/application/application'
 
 const auditLogger = auditLoggerFactory('channels')
 const reqAvatarFile = createReqFiles([ 'avatarfile' ], MIMETYPES.IMAGE.MIMETYPE_EXT, { avatarfile: CONFIG.STORAGE.TMP_DIR })
@@ -98,7 +99,7 @@ videoChannelRouter.get('/:nameWithHost/videos',
   asyncMiddleware(videoChannelsNameWithHostValidator),
   paginationValidator,
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   setDefaultPagination,
   optionalAuthenticate,
   commonVideosFiltersValidator,
diff --git a/server/controllers/api/videos/index.ts b/server/controllers/api/videos/index.ts
index 1dfd7c7a0..c05acfd2f 100644
--- a/server/controllers/api/videos/index.ts
+++ b/server/controllers/api/videos/index.ts
@@ -1,11 +1,24 @@
 import * as express from 'express'
+import { move } from 'fs-extra'
 import { extname } from 'path'
+import toInt from 'validator/lib/toInt'
+import { addOptimizeOrMergeAudioJob } from '@server/helpers/video'
+import { createTorrentAndSetInfoHash } from '@server/helpers/webtorrent'
+import { changeVideoChannelShare } from '@server/lib/activitypub/share'
+import { getVideoActivityPubUrl } from '@server/lib/activitypub/url'
+import { getVideoFilePath } from '@server/lib/video-paths'
+import { getServerActor } from '@server/models/application/application'
+import { MVideoDetails, MVideoFullLight } from '@server/types/models'
 import { VideoCreate, VideoPrivacy, VideoState, VideoUpdate } from '../../../../shared'
+import { ThumbnailType } from '../../../../shared/models/videos/thumbnail.type'
+import { VideoFilter } from '../../../../shared/models/videos/video-query.type'
+import { auditLoggerFactory, getAuditIdFromRes, VideoAuditView } from '../../../helpers/audit-logger'
+import { resetSequelizeInstance } from '../../../helpers/database-utils'
+import { buildNSFWFilter, createReqFiles, getCountVideos } from '../../../helpers/express-utils'
 import { getMetadataFromFile, getVideoFileFPS, getVideoFileResolution } from '../../../helpers/ffmpeg-utils'
 import { logger } from '../../../helpers/logger'
-import { auditLoggerFactory, getAuditIdFromRes, VideoAuditView } from '../../../helpers/audit-logger'
 import { getFormattedObjects } from '../../../helpers/utils'
-import { autoBlacklistVideoIfNeeded } from '../../../lib/video-blacklist'
+import { CONFIG } from '../../../initializers/config'
 import {
   DEFAULT_AUDIO_RESOLUTION,
   MIMETYPES,
@@ -14,9 +27,15 @@ import {
   VIDEO_LICENCES,
   VIDEO_PRIVACIES
 } from '../../../initializers/constants'
+import { sequelizeTypescript } from '../../../initializers/database'
+import { sendView } from '../../../lib/activitypub/send/send-view'
 import { federateVideoIfNeeded, fetchRemoteVideoDescription } from '../../../lib/activitypub/videos'
 import { JobQueue } from '../../../lib/job-queue'
+import { Notifier } from '../../../lib/notifier'
+import { Hooks } from '../../../lib/plugins/hooks'
 import { Redis } from '../../../lib/redis'
+import { createVideoMiniatureFromExisting, generateVideoMiniature } from '../../../lib/thumbnail'
+import { autoBlacklistVideoIfNeeded } from '../../../lib/video-blacklist'
 import {
   asyncMiddleware,
   asyncRetryTransactionMiddleware,
@@ -26,7 +45,7 @@ import {
   optionalAuthenticate,
   paginationValidator,
   setDefaultPagination,
-  setDefaultSort,
+  setDefaultVideosSort,
   videoFileMetadataGetValidator,
   videosAddValidator,
   videosCustomGetValidator,
@@ -35,37 +54,18 @@ import {
   videosSortValidator,
   videosUpdateValidator
 } from '../../../middlewares'
+import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update'
 import { TagModel } from '../../../models/video/tag'
 import { VideoModel } from '../../../models/video/video'
 import { VideoFileModel } from '../../../models/video/video-file'
 import { abuseVideoRouter } from './abuse'
 import { blacklistRouter } from './blacklist'
-import { videoCommentRouter } from './comment'
-import { rateVideoRouter } from './rate'
-import { ownershipVideoRouter } from './ownership'
-import { VideoFilter } from '../../../../shared/models/videos/video-query.type'
-import { buildNSFWFilter, createReqFiles, getCountVideos } from '../../../helpers/express-utils'
-import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update'
 import { videoCaptionsRouter } from './captions'
+import { videoCommentRouter } from './comment'
 import { videoImportsRouter } from './import'
-import { resetSequelizeInstance } from '../../../helpers/database-utils'
-import { move } from 'fs-extra'
+import { ownershipVideoRouter } from './ownership'
+import { rateVideoRouter } from './rate'
 import { watchingRouter } from './watching'
-import { Notifier } from '../../../lib/notifier'
-import { sendView } from '../../../lib/activitypub/send/send-view'
-import { CONFIG } from '../../../initializers/config'
-import { sequelizeTypescript } from '../../../initializers/database'
-import { createVideoMiniatureFromExisting, generateVideoMiniature } from '../../../lib/thumbnail'
-import { ThumbnailType } from '../../../../shared/models/videos/thumbnail.type'
-import { Hooks } from '../../../lib/plugins/hooks'
-import { MVideoDetails, MVideoFullLight } from '@server/types/models'
-import { createTorrentAndSetInfoHash } from '@server/helpers/webtorrent'
-import { getVideoFilePath } from '@server/lib/video-paths'
-import toInt from 'validator/lib/toInt'
-import { addOptimizeOrMergeAudioJob } from '@server/helpers/video'
-import { getServerActor } from '@server/models/application/application'
-import { changeVideoChannelShare } from '@server/lib/activitypub/share'
-import { getVideoActivityPubUrl } from '@server/lib/activitypub/url'
 
 const auditLogger = auditLoggerFactory('videos')
 const videosRouter = express.Router()
@@ -105,7 +105,7 @@ videosRouter.get('/privacies', listVideoPrivacies)
 videosRouter.get('/',
   paginationValidator,
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   setDefaultPagination,
   optionalAuthenticate,
   commonVideosFiltersValidator,
diff --git a/server/controllers/feeds.ts b/server/controllers/feeds.ts
index bfcd3fe36..f14c0d316 100644
--- a/server/controllers/feeds.ts
+++ b/server/controllers/feeds.ts
@@ -1,21 +1,21 @@
 import * as express from 'express'
+import * as Feed from 'pfeed'
+import { buildNSFWFilter } from '../helpers/express-utils'
+import { CONFIG } from '../initializers/config'
 import { FEEDS, ROUTE_CACHE_LIFETIME, THUMBNAILS_SIZE, WEBSERVER } from '../initializers/constants'
 import {
   asyncMiddleware,
   commonVideosFiltersValidator,
-  setDefaultSort,
+  feedsFormatValidator,
+  setDefaultVideosSort,
+  setFeedFormatContentType,
   videoCommentsFeedsValidator,
   videoFeedsValidator,
-  videosSortValidator,
-  feedsFormatValidator,
-  setFeedFormatContentType
+  videosSortValidator
 } from '../middlewares'
-import { VideoModel } from '../models/video/video'
-import * as Feed from 'pfeed'
 import { cacheRoute } from '../middlewares/cache'
+import { VideoModel } from '../models/video/video'
 import { VideoCommentModel } from '../models/video/video-comment'
-import { buildNSFWFilter } from '../helpers/express-utils'
-import { CONFIG } from '../initializers/config'
 
 const feedsRouter = express.Router()
 
@@ -34,7 +34,7 @@ feedsRouter.get('/feeds/video-comments.:format',
 
 feedsRouter.get('/feeds/videos.:format',
   videosSortValidator,
-  setDefaultSort,
+  setDefaultVideosSort,
   feedsFormatValidator,
   setFeedFormatContentType,
   asyncMiddleware(cacheRoute({
diff --git a/server/middlewares/sort.ts b/server/middlewares/sort.ts
index fcbb2902c..609046a46 100644
--- a/server/middlewares/sort.ts
+++ b/server/middlewares/sort.ts
@@ -2,6 +2,7 @@ import * as express from 'express'
 import { SortType } from '../models/utils'
 
 const setDefaultSort = setDefaultSortFactory('-createdAt')
+const setDefaultVideosSort = setDefaultSortFactory('-publishedAt')
 
 const setDefaultVideoRedundanciesSort = setDefaultSortFactory('name')
 
@@ -33,6 +34,7 @@ function setBlacklistSort (req: express.Request, res: express.Response, next: ex
 export {
   setDefaultSort,
   setDefaultSearchSort,
+  setDefaultVideosSort,
   setDefaultVideoRedundanciesSort,
   setBlacklistSort
 }