portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Sean Taylor <da...@bluesunrise.com>
Subject preferences performance tuning
Date Mon, 10 Dec 2007 05:44:37 GMT
Ran performance tests here on the preferences, on about 3000+ prefs  
(10000 nodes)

NO INDEX 		: 62 seconds
FULL_PATH 	: 34 seconds
NODE_FK             : 35 seconds
BOTH			:   8 seconds

Where:

NO INDEX -- out of the box.

FULL_PATH :

ALTER TABLE  PREFS_NODE
  ADD INDEX FULL_PATH_IDX(`FULL_PATH`);

NODE_FK:

ALTER TABLE  PREFS_PROPERTY_VALUE
  ADD CONSTRAINT `PREFS_PARENT_FK` FOREIGN KEY `PREFS_PARENT_FK`  
(`NODE_ID`)
     REFERENCES `PREFS_NODE` (`NODE_ID`)
     ON DELETE CASCADE;


I'd like to propose adding both indexes (NODE_FK, FULL_PATH) to the  
schema/prefs.xml like this:

(1) UNCOMMENT: I think the wrong FK was commented out.:

         <!-- Still an issue with OJB 1.0.3 when deleting M-N.  
Foreign Key Violation.
         <foreign-key foreignTable="PREFS_NODE">
             <reference foreign="NODE_ID" local="NODE_ID"/>
         </foreign-key>
         -->
and adding a onDelete="cascade" constraint
Surely OJB can now handle one-to-many cases like this...

Here is a self-referential FK, but it is not commented out:

         <foreign-key foreignTable="PREFS_NODE" name="FK_PREFS_NODE_1">
             <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
         </foreign-key>

(2) I also suggest adding:

         <index name="IX_FULLPATH_1">
             <index-column name="FULL_PATH"/>
         </index>

Note that all tests pass with these indexes enabled on the following  
databases:

* MySQL 5
....


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message