From f159cf688fdaa6a333cc18ccb203b44663889904 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Tue, 29 Sep 2009 14:44:17 +0200 Subject: [PATCH] Remove the inx4 index for SQLite2/3 (FilenameId, PathId) on File table. Replace it by an index over (JobId, PathId, FilenameId). It is useless for common usage, and causes performance issues. This index fragments over the time and the update cost grows very quickly. (This index is not present on Mysql schema) --- bacula/src/cats/make_sqlite3_tables.in | 3 +-- bacula/src/cats/make_sqlite_tables.in | 3 +-- 2 files changed, 2 insertions(+), 4 deletions(-) diff --git a/bacula/src/cats/make_sqlite3_tables.in b/bacula/src/cats/make_sqlite3_tables.in index 99b5dfd9e6..78b97c7fef 100644 --- a/bacula/src/cats/make_sqlite3_tables.in +++ b/bacula/src/cats/make_sqlite3_tables.in @@ -40,14 +40,13 @@ CREATE TABLE File ( ); CREATE INDEX inx3 ON File (JobId); -CREATE INDEX inx4 ON File (FilenameId, PathId); +CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId); -- -- Possibly add one or more of the following indexes -- if your Verifies are too slow. -- -- CREATE INDEX inx4 ON File (PathId); -- CREATE INDEX inx5 ON File (FileNameId); --- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId); CREATE TABLE Job ( JobId INTEGER, diff --git a/bacula/src/cats/make_sqlite_tables.in b/bacula/src/cats/make_sqlite_tables.in index 9bd0a481e8..e8693f0715 100644 --- a/bacula/src/cats/make_sqlite_tables.in +++ b/bacula/src/cats/make_sqlite_tables.in @@ -38,14 +38,13 @@ CREATE TABLE File ( ); CREATE INDEX inx3 ON File (JobId); -CREATE INDEX inx4 ON File (FilenameId, PathId); +CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId); -- -- Possibly add one or more of the following indexes -- if your Verifies are too slow. -- -- CREATE INDEX inx4 ON File (PathId); -- CREATE INDEX inx5 ON File (FileNameId); --- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId); CREATE TABLE Job ( JobId INTEGER, -- 2.39.5