With mass user creation or file upload (adding VerifyUserData and UploadCleanup entires) the oc_jobs table might grow into as size where the query if a given job exists is not that optimal and would need to go over all rows of a given job class and apply the where check for the argument.
| 45 | OCA\DAV\BackgroundJob\UploadCleanup | {"uid":"admin","folder":"303f7206-f3c1-49df-a4c7-c997e8ee21e2"} | 0 | 1629194333 | 0 | 0 |
| 46 | OCA\DAV\BackgroundJob\UploadCleanup | {"uid":"admin","folder":"44c68b7a-d7c2-46d7-b779-091e49992ffd"} | 0 | 1629194336 | 0 | 0 |
| 47 | OCA\DAV\BackgroundJob\UploadCleanup | {"uid":"admin","folder":"314a76ee-17a6-4fe3-bff3-0832db0601b2"} | 0 | 1629194339 | 0 | 0 |
|
$query = $this->connection->getQueryBuilder(); |
|
$query->select('id') |
|
->from('jobs') |
|
->where($query->expr()->eq('class', $query->createNamedParameter($class))) |
|
->andWhere($query->expr()->eq('argument', $query->createNamedParameter($argument))) |
|
->setMaxResults(1); |
We cannot add an index to argument as it is a varchar(4000). I'd suggest to add a separate column to store a hashed value of the arguments here in order to have an indexable coulumn which allows to check for existance of a specific job in the table right away.
Any objections @nextcloud/server ?
With mass user creation or file upload (adding VerifyUserData and UploadCleanup entires) the oc_jobs table might grow into as size where the query if a given job exists is not that optimal and would need to go over all rows of a given job class and apply the where check for the argument.
server/lib/private/BackgroundJob/JobList.php
Lines 138 to 143 in 215aef3
We cannot add an index to argument as it is a varchar(4000). I'd suggest to add a separate column to store a hashed value of the arguments here in order to have an indexable coulumn which allows to check for existance of a specific job in the table right away.
Any objections @nextcloud/server ?