SQL commands for missing indices

nusspe · August 10, 2023, 9:24pm

Support intro

I search for sql-commands for creating missing indices

Nextcloud version 27.0.2
Operating system and version Webhosting environment

wwe · August 11, 2023, 10:18am

try searching the forum and the docs… there miraids of topics talking about “occ db:add-missing-indices"

jtr · August 13, 2023, 1:10pm

It’s fully documented in the “How to upgrade” section of the Admin Manual. Here’s the direct link to the spot you’re looking for.

P.S. The manual is fully searchable by keyword.

nusspe · August 13, 2023, 7:37pm

Many Thanks for your answers, but I can’t run occ in my environment so I serach for the SQL-Commands alter table…
or can anybody tell mye where can I find the php-script add-missing-indices?
Best regards
Peter

ernolf · August 13, 2023, 7:58pm

Voila (for your version 27.0.2):

github.com

nextcloud/server/blob/v27.0.2/core/Command/Db/AddMissingIndices.php

<?php

declare(strict_types=1);

/**
 * @copyright Copyright (c) 2017 Bjoern Schiessle <bjoern@schiessle.org>
 *
 * @author Bjoern Schiessle <bjoern@schiessle.org>
 * @author Christoph Wurst <christoph@winzerhof-wurst.at>
 * @author Joas Schilling <coding@schilljs.com>
 * @author Julius Härtl <jus@bitgrid.net>
 * @author Mario Danic <mario@lovelyhq.com>
 * @author Morris Jobke <hey@morrisjobke.de>
 * @author Robin Appelman <robin@icewind.nl>
 * @author Roeland Jago Douma <roeland@famdouma.nl>
 * @author Thomas Citharel <nextcloud@tcit.fr>
 *
 * @license GNU AGPL version 3 or any later version
 *
 * This program is free software: you can redistribute it and/or modify

This file has been truncated. show original

There is even an option “–dry-run” to provide the SQL queries instead of running them.

But unfortunately that does not work on mysql instances (I filed a bugreport for that already) but on a postgre SQL instance I got the query. It looks like:

Adding additional textstep_session index to the oc_text_steps table, this can take some time…

CREATE INDEX textstep_session ON oc_text_steps (session_id);

So looking in the code wil show, what indices have to be added and my example should help you to generate the SQL queries for them.

Hope that helps,
much luck!

Mornsgrans · August 13, 2023, 8:41pm

Can you tell us more details about this? - If you have access to SQL command line, you also should have access to occ, if you use the correct syntax and path.

ernolf · August 13, 2023, 8:45pm

Tell me which Indices your nextcloud is missing exactly and I will try to get it in the right form for you:

here one more example:

github.com

nextcloud/server/blob/v27.0.2/core/Command/Db/AddMissingIndices.php#L487


      
          			if (!$table->hasIndex('preferences_app_key')) {
          				$output->writeln('<info>Adding preferences_app_key index to the oc_preferences table, this can take some time...</info>');
          
          				$table->addIndex(['appid', 'configkey'], 'preferences_app_key');
          				$this->connection->migrateToSchema($schema->getWrappedSchema());
          				$updated = true;
          				$output->writeln('<info>oc_properties table updated successfully.</info>');
          			}
          		}
          
          		$output->writeln('<info>Check indices of the oc_mounts table.</info>');
          		if ($schema->hasTable('mounts')) {
          			$table = $schema->getTable('mounts');
          			if (!$table->hasIndex('mounts_class_index')) {
          				$output->writeln('<info>Adding mounts_class_index index to the oc_mounts table, this can take some time...</info>');
          
          				$table->addIndex(['mount_provider_class'], 'mounts_class_index');
          				$this->connection->migrateToSchema($schema->getWrappedSchema());
          				$updated = true;
          				$output->writeln('<info>oc_mounts table updated successfully.</info>');
          			}

to add the mounts_class_index index on the mount_provider_class column in the oc_mounts table:

CREATE INDEX mounts_class_index ON oc_mounts (mount_provider_class);

and to add the mounts_user_root_path_index index on the user_id, root_id and mount_point column in the oc_mounts table:

CREATE INDEX mounts_user_root_path_index ON oc_mounts (user_id, root_id, mount_point);

ernolf

ernolf · August 13, 2023, 8:51pm

Even though you didn’t ask me: Some providers do not allow ssh access while the database can be managed with other interfaces.