authtoken table performance considerations
Open
Nobody has claimed this yet.
2. developing
enhancement
feature: authentication
feature: database
performance 🚀
- Dominant language
- PHP
- Stars
- 36.9k
- Forks
- 5.2k
- Avg merge
- 2d 3h
- Merged PRs (30d)
- 713
Description
How to use GitHub
- Please use the 👍 reaction to show that you are interested into the same feature.
- Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
- Subscribe to receive notifications on status change and new comments.
Is your feature request related to a problem? Please describe
As the admin of a Nextcloud instance I notice lots of queries on the oc_authtoken table.
SELECT * FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2)UPDATE oc_authtoken SET last_activity=:dcValue1 WHERE id=:dcValue2 AND last_activity <= :dcValue3
26.0.9 oc_authtoken queries study
| Executions | Query |
|---|---|
| 625507 | SELECT * FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2) |
| 28115 | SELECT `name` FROM `oc_authtoken` WHERE (`uid` = :dcValue1) AND (`last_activity` >= :dcValue2) |
| 28002 | UPDATE `oc_authtoken` SET `last_activity` = :dcValue1 WHERE (`id` = :dcValue2) AND (`last_activity` < :dcValue3) |
| 15126 | UPDATE `oc_authtoken` SET `last_check` = :dcValue1 WHERE `id` = :dcValue2 |
| 4492 | SELECT * FROM `oc_authtoken` WHERE (`uid` = :dcValue1) AND (`version` = :dcValue2) LIMIT 1000 |
| 2020 | DELETE FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2) |
| 295 | SELECT * FROM `oc_authtoken` WHERE `uid` = :dcValue1 ORDER BY `id` ASC LIMIT 1 |
| 295 | INSERT INTO `oc_authtoken` (`uid`, `login_name`, `public_key`, `private_key`, `password`, `password_hash`, `name`, `token`, `type`, `remember`, `last_activity`, `last_check`, `version`) VALUES(:dcValue1, :dcValue2, :dcValue3, :dcValue4, :dcValue5, :dcValue6, :dcValue7, :dcValue8, :dcValue9, :dcValue10, :dcValue11, :dcValue12, :dcValue13) |
| 48 | DELETE FROM `oc_authtoken` WHERE (`last_activity` < :dcValue1) AND (`type` = :dcValue2) AND (`remember` = :dcValue3) AND (`version` = :dcValue4) |
| 33 | DELETE FROM `oc_authtoken` WHERE `id` = :dcValue1 |
| 9 | SELECT * FROM `oc_authtoken` WHERE (`id` = :dcValue1) AND (`version` = :dcValue2) |
| 6 | UPDATE `oc_authtoken` SET `password` = :dcValue1, `password_hash` = :dcValue2 WHERE `id` = :dcValue3 |
Describe the solution you'd like
Database improvements
- Change LONGTEXT columns name, scope, private_key and public_key to varchar. LONGTEXT is stored outside the table data, VARCHAR is stored inline. Tricky: find reasonable maximum length for VARCHAR to avoid truncation problems.
- New unique index for id+last_activity. The covering index allows the DB to run the last_activity condition without reading table data.
- Drop superfluous indices. Index updates cause database load. Unless an index gives a clear benefit, we can drop it to make INSERT, DELETE and some UPDATE queries cheaper.
- Make last_activity (and last_check) NOT NULL. It has a default value of 0 and we never actually make use of NULL. Yet NULL is theoretically possible and might influence the database's decision on whether to use an index of the nullable column.
- Update last_check and last_activity together
Application improvements
- https://github.com/nextcloud/server/issues/42823
- https://github.com/nextcloud/notifications/issues/1769
- https://github.com/nextcloud/server/issues/42589
- https://github.com/nextcloud/server/issues/43260
- Do not UPDATE authtoken.last_check if no password check is performed https://github.com/nextcloud/server/pull/44670
Describe alternatives you've considered
N/a
Additional context
N/a
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the authtoken table definition and the query study in this issue; review the listed schema and application-improvement links. Determine which index, column-type, nullability, and update changes remain open, then validate their effect against the enumerated queries without duplicating completed links.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- php, sql
- Domain
- database
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100