[vhffs-dev] [442] Added two indexes, one on vhffs_object(state) (overall perfs), one on vhffs_httpd(substr(servername, 1, 1)) to improve first letter searches

[subversion@xxxxxxxxx]

Revision: 442
Author:   beuss
Date:     2007-02-04 14:08:07 +0000 (Sun, 04 Feb 2007)

Log Message:
-----------
Added two indexes, one on vhffs_object(state) (overall perfs), one on vhffs_httpd(substr(servername, 1, 1)) to improve first letter searches

Modified Paths:
--------------
    branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql
    branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql


Modified: branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql	2007-02-04 14:01:30 UTC (rev 441)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql	2007-02-04 14:08:07 UTC (rev 442)
@@ -33,4 +33,9 @@
 ALTER TABLE vhffs_object ALTER owner_uid DROP NOT NULL;
 ALTER TABLE vhffs_users ALTER gid DROP NOT NULL;
 
+-- This index drastically improves performances on get_used_letters
+CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1)); 
+-- state is massively used in WHERE clause, this index improves overall performances
+CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
+
 DROP TABLE vhffs_largefile CASCADE;

Modified: branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql	2007-02-04 14:01:30 UTC (rev 441)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql	2007-02-04 14:08:07 UTC (rev 442)
@@ -348,6 +348,10 @@
 ALTER TABLE vhffs_cvs ADD CONSTRAINT vhffs_cvs_unique_cvsroot UNIQUE (cvsroot);
 ALTER TABLE vhffs_httpd ADD CONSTRAINT vhffs_httpd_unique_servername UNIQUE (servername);
 
+-- This index drastically improves performances on get_used_letters
+CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
+-- state is massively used in WHERE clause, this index improves overall performances
+CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
 
 /****** Non primary key constraints.
       Defining foreign keys here allow to create tables in any order.