duplicati/Duplicati/Library/Main/Database/Database schema/11. Add Block indices.sql at master - Public/duplicati - A git service for evolab

Public/duplicati

mirror of https://github.com/duplicati/duplicati.git synced 2025-11-28 11:30:24 +08:00

Kenneth Hsu 6638af10e7 Add indices to Block table to improve query performance.

The index on Block.Size improves the performance of the query that
checks that the user has not altered the block size of the backup
configuration.

The index on Block.Hash and Block.VolumeID improves the performance of
the query that obtains the blocklists.

There are anecdotes that these indices can drastically improve
performance (queries that used to take hours completed in seconds with
the index):

https://forum.duplicati.com/t/repairing-a-big-database/8174/4
https://github.com/duplicati/duplicati/issues/3884#issuecomment-612296016

This fixes issue #3884.

2020-04-11 11:47:40 -07:00

4 lines

189 B

SQL

Raw Permalink Blame History

 CREATE INDEX IF NOT EXISTS "BlockSize" ON "Block" ("Size");
 CREATE UNIQUE INDEX IF NOT EXISTS "BlockHashVolumeID" ON "Block" ("Hash", "VolumeID");
 UPDATE "Version" SET "Version" = 11;