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:

src/upgrade-db-to-1.7-postgres.sql
1
/*
2
 * New indexes to make queries avoid full table scans.
3
 */
4
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
5
CREATE INDEX xml_access_idx1 ON xml_access (lower(principal_name));
6
CREATE INDEX xml_access_idx2 ON xml_access (permission);
7
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);
8
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);
9
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);
10
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));
11

  
12
/** done */
0 13

  
src/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
src/edu/ucsb/nceas/metacat/QueryTerm.java
140 140
        // Add appropriate wildcards to search string
141 141
        String searchexpr = null;
142 142
        if (searchmode.equals("starts-with")) {
143
            searchexpr = nodedataterm + " LIKE '" + casevalue + "%' ";
143
            searchexpr = nodedataterm + " = '" + casevalue + "%' ";
144 144
        } else if (searchmode.equals("ends-with")) {
145
            searchexpr = nodedataterm + " LIKE '%" + casevalue + "' ";
145
            searchexpr = nodedataterm + " = '%" + casevalue + "' ";
146 146
        } else if (searchmode.equals("contains")) {
147 147
            if (!casevalue.equals("%")) {
148
                searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
148
                searchexpr = nodedataterm + " = '%" + casevalue + "%' ";
149 149
            } else {
150
                searchexpr = nodedataterm + " LIKE '" + casevalue + "' ";
150
                searchexpr = nodedataterm + " = '" + casevalue + "' ";
151 151
                // find percentage symbol
152 152
                percentageSymbol = true;
153 153
            }
154 154
        } else if (searchmode.equals("not-contains")) {
155 155
        	notEqual = true;
156
            searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
156
            searchexpr = nodedataterm + " = '%" + casevalue + "%' ";
157 157
        } else if (searchmode.equals("equals")) {
158 158
            searchexpr = nodedataterm + " = '" + casevalue + "' ";
159 159
        } else if (searchmode.equals("isnot-equal")) {
......
208 208
        		self.append("SELECT DISTINCT docid from xml_path_index WHERE");
209 209
        		self.append(" docid NOT IN (Select docid FROM xml_path_index WHERE ");
210 210
        		self.append(searchexpr);
211
        		self.append("AND path LIKE '" + pathexpr + "') ");
211
        		self.append("AND path = '" + pathexpr + "') ");
212 212
        	} else {
213 213
        		self.append("SELECT DISTINCT docid FROM xml_path_index WHERE ");
214 214
        		self.append(searchexpr);
215
        		self.append("AND path LIKE '" + pathexpr + "' ");	
215
        		self.append("AND path = '" + pathexpr + "' ");	
216 216
        	}
217 217

  
218 218
        } else {

Also available in: Unified diff