How to add missing database indexes in Nextcloud

Nextcloud can report missing optional database indexes after upgrades or app schema changes. Those indexes are registered by the server code but are not always added automatically during the upgrade path, because index creation can take time on large tables.

The supported repair path is the occ db:add-missing-indices command. It compares the registered optional indexes with the current database schema, can preview the SQL with --dry-run, and adds only the missing indexes it knows about.

Run the command as the web-server user from the installed Nextcloud directory. A successful repair changes the Database missing indices setup-check row to None; unrelated setup warnings, such as cron, mail, headers, or database backend warnings, may still need separate follow-up work.

Steps to add missing Nextcloud database indexes with occ:

Change to the Nextcloud installation directory.
```
$ cd /var/www/nextcloud
```
Use the directory that contains the occ file. Archive, package, and container installs may use paths such as /var/www/html or /usr/share/webapps/nextcloud instead.

Confirm that setup checks report missing database indexes.

$ sudo -E -u www-data php occ setupchecks
##### snipped #####
	database:
		✓ Database missing columns: None
		⚠ Database missing indices: Detected some missing optional indices. Occasionally new indices are added (by Nextcloud or installed applications) to improve database performance. Adding indices can sometimes take awhile and temporarily hurt performance so this is not done automatically during upgrades. Once the indices are added, queries to those tables should be faster. Use the command `occ db:add-missing-indices` to add them.
Missing indices:
 "fs_size" in table "filecache"
		✓ Database missing primary keys: None
##### snipped #####

The exact index names depend on the installed Nextcloud version and enabled apps. Fix this warning separately from unrelated setup-check rows.
Related: How to check Nextcloud administration overview warnings

Confirm that a current database backup is available.

Adding indexes changes the database schema. A failed migration, interrupted database service, or full disk can require restoring the database from backup.

Related: How to create a Nextcloud backup

Preview the planned index changes.

$ sudo -E -u www-data php occ db:add-missing-indices --dry-run
Adding additional fs_size index to the oc_filecache table, this can take some time...
oc_filecache table updated successfully.
##### snipped #####
CREATE INDEX fs_size ON oc_filecache (size);

In dry-run mode, the command prints the migration plan without applying it. If there are no registered missing optional indexes, the command can exit without output.

Add the missing indexes.
```
$ sudo -E -u www-data php occ db:add-missing-indices
Adding additional fs_size index to the oc_filecache table, this can take some time...
oc_filecache table updated successfully.
```
Run this during a maintenance window on busy servers. Index creation can temporarily slow writes or lock a table while the database engine builds the index.

Re-run setup checks for the database result.

$ sudo -E -u www-data php occ setupchecks
##### snipped #####
	database:
		✓ Database missing columns: None
		✓ Database missing indices: None
		✓ Database missing primary keys: None
		✓ Database pending bigint migrations: None
##### snipped #####

occ setupchecks may still return a non-zero exit status when other warnings remain. The missing-index repair is complete when the database row reports Database missing indices: None.