Project

General

Profile

Revision 3052

Added by Matt Jones over 14 years ago

Modifications to the table definitions to add indices that should be used
when searching the database. Added an upgrade script to create these
indices as well. Modified QueryTerm.java to use the '=' comparator rather
than 'LIKE' so that we can do index scans rather than table scans. This needs
to be tested.

View differences:

xmltables-postgres.sql
166 166
 * Index of <docid,doctype> in xml_document
167 167
 */
168 168
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
169
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
169 170

  
170 171
/*
171 172
 * Revised Documents -- table to store XML documents saved after an UPDATE
......
217 218
   CONSTRAINT xml_access_accessfileid_fk
218 219
		FOREIGN KEY (accessfileid) REFERENCES xml_documents
219 220
);
221
CREATE INDEX xml_access_idx1 ON xml_access (lower(principal_name));
222
CREATE INDEX xml_access_idx2 ON xml_access (permission);
223
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);
224
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);
225
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);
220 226

  
221 227
/*
222 228
 * Index of Nodes -- table to store precomputed paths through tree for
......
357 363
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
358 364
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
359 365
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
366
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));
360 367

  
361 368
/*
362 369
 * harvest_site_schedule -- table to store harvest sites and schedule info

Also available in: Unified diff