authtoken table performance considerations

[ChristophWurst]

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.

1. SELECT * FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2)
2. 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

1. 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.
2. New unique index for id+last_activity. The covering index allows the DB to run the last_activity condition without reading table data.
3. 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.
   • [Bug]: Redundant (mysql) index oc_authtoken.authtoken_id_version_idx #43111
4. 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.
5. Update last_check and last_activity together
   • Avoid updating the same oc_authtoken row twice #45026

Application improvements

1. Cache authtoken lookup by token #42823
2. Cache informaton about Talk desktop usage notifications#1769
3. [Bug]: Two useless authtoken database queries for every anonymous request #42589
4. Set authtoken.private_key and authtoken.public_key NULL for auth.storeCryptedPassword=true #43260
5. Do not UPDATE authtoken.last_check if no password check is performed fix(session): Do not update authtoken last_check for passwordless #44670

Describe alternatives you've considered

N/a

Additional context

N/a

[juliusknorr]

Another insight I had noted but not filed or investigated:

it seems that we may run two update queries in one request just after each other

UPDATE `oc_authtoken` SET `last_check` = :dcValue1 WHERE `id` = :dcValue2
UPDATE `oc_authtoken` SET `last_activity` = :dcValue1 WHERE (`id` = :dcValue2) AND (`last_activity` < :dcValue3)

[ChristophWurst]

Unverified idea: we could combine private/public key into one database column, either as PEM or our own format. Then there would only be one external blob.

[juliusknorr]

Another insight I had noted but not filed or investigated:

it seems that we may run two update queries in one request just after each other

UPDATE `oc_authtoken` SET `last_check` = :dcValue1 WHERE `id` = :dcValue2
UPDATE `oc_authtoken` SET `last_activity` = :dcValue1 WHERE (`id` = :dcValue2) AND (`last_activity` < :dcValue3)

#45026 would address this