From f3f472daae4da8860968870b1c3c604de66bcf10 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 8f1ce00833..98b037d868 100644 --- a/bacula/src/cats/make_sqlite3_tables.in +++ b/bacula/src/cats/make_sqlite3_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, 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