Database is missing some indexes - create indexes for SQLite without occ?

ℹ️ Support
, ,
1

On a Nextcloud test server i use Nextcloud 29.0.0 RC5. The server is only a webspace without occ and i use SQLite instead of MariaDB. I realize that this is not a standard configuration. I have other Nextcloud instances where I use MariaDB and can use occ.

The database is missing some indexes. Due to the fact that adding indexes on big tables could take some time they were not added automatically. By running “occ db:add-missing-indices” those missing indexes could be added manually while the instance keeps running. Once the indexes are added queries to those tables are usually much faster. Missing optional index “oc_npushhash_di” in table “notifications_pushhash”.

I would still like to know if anyone knows a way to perform this customization under SQLite. Or do I end up with a local Nextcloud copy at home, where I copy the SQLite file, have it changed and then copy it back to the webspace?

I look forward to creative ideas.

2

It would be nice to have all the sql/sqlite command to change the structure without the need of installing php and all the depencies. Then the local transfer change and transfer back would be quite quick.

1 Like
3

Thanks. Nextcloud should be interested in minimize the need of occ-commands.

4

Except for some maintenance stuff that you want to do on CLI as well, it is mainly for task that risk taking longer where you risk to into timeouts when running such commands over the webinterface. Especially for backup, updates and database operations this is the case, and if the script runs into a timeout, it potentially leaves your setup in a unusable state.

You could propose workaround in such cases, problem this is mostly an issue on webhosting. Unfortunately, these are probably the users the less experienced doing such stuff manually. And to be fair, you need to document and test all these procedures. On top of the general issues with hosting: How To: Get Help on Shared Webhosting

If it technically works (even with limits), I don’t think we should prevent users from using it (if they are aware of these limits). But it probably needs a few more experienced users like you, that help doing such things, document it, …

1 Like
5

I don’t have a solution for doing this without occ, but if your hosting provider allows you to set up cronjobs for your webroot, which I suspect almost all of them do, you could add the occ db:add-missing-indices command to a cronjob, run it once and then delete it afterwards.

1 Like
6

There was an app however to run the occ command from the web interface:

but it was discontinued, and there were problems running long during commands.