[jboss-jira] [JBoss JIRA] Commented: (JBPM-735) add indexes to foreign keys

[Christian Bauer (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBPM-735?page=comments#action_12341484 ] 
            
Christian Bauer commented on JBPM-735:
--------------------------------------

-1

A DBA has to decide which indexes are created, what type of index is needed (there are many), and how these indexes are stored (tablespaces, etc). A DBA ideally supplies his optimizations _on top_ of the Hibernate-generated schema. This can be as trivial as an auxiliary SQL DDL script, it can even be included in Hibernate metadata as <database-object>s.

It should be clear that this script is highly dependent on the actual environment the application is deployed in. Hibernate does not generate an optimized schema, Hibernate does not claim that it would optimize the schema for all environments. Hibernates responsibility ends with the schema that implements the logical model, any DDL optimization for the actual physical deployment is out of scope.


> add indexes to foreign keys
> ---------------------------
>
>                 Key: JBPM-735
>                 URL: http://jira.jboss.com/jira/browse/JBPM-735
>             Project: JBoss jBPM
>          Issue Type: Feature Request
>          Components: Core Engine
>            Reporter: Tom Baeyens
>         Assigned To: Tom Baeyens
>             Fix For: jBPM 3.2 alpha 2
>
>
> most databases don't generate implicit indexes for foreign keys.  so it's better to change the default and specify indexes on all foreign keys that can be used in joins.
> here's the mail conversation that led to this issue:
> > max, sorry for going out-of-bound.  david doesn't yet have access to 
> > salesforce.
> >
> > | No database I know of will create indexes by default for FKs, but 
> > | that doesn't rule it out I suppose.  I'd think that the risk is low 
> > | though.
> > | Even if it were the case, in my opinion it is up to Hibernate to 
> > | recognize the existing indexes and not generate DDL for them.
> No, not up to hibernate. Up to the DBA :)
> > should we consider creating the FK indexes in jbpm by default and only 
> > document on which databases this would create a problem of duplicate 
> > indexes ?
> That might be easier yes. I also don't know which db's that does create the index or not but in any case if it would always make sense to create the index why don't the database you know don't do it ?
> In any case, it is all controllable via the index attribute and if you find that the db's you deploy most to should have the index created then that makes sense for you.
> That said having a global or dialect controlled flag for it *might* make sense...but that is a future addition.
> /max
> > regards, tom.
> >
> > | -----Original Message-----
> > | From: David Lloyd 
> > | Subject: Re: New case comment notification. Case Number 00010803
> > |
> > | On Wed, 2006-08-16 at 09:20 +0000, Thomas Baeyens wrote:
> > | > Thomas Baeyens has added a comment to case 00010803 :
> > | "Foreign keys in JBPM Schema are not indexed?!".  Please read the 
> > | comment below and then click on the link to respond appropriately.
> > | >
> > | > Comment:
> > | > we didn't do this explicitly for PK's because most
> > | databases generate
> > | > these automatically.  i probably assumed the same was true for FK's.
> > | >
> > | > question to the hibernate team:  in general, should we be adding 
> > | > indexes on all our foreign key relations ?  is there are
> > | catch why we
> > | > wouldn't want to do this ?
> > |
> > | I would personally strongly recommend adding indexes for all foreign 
> > | keys.  The only downside is that they take up more storage in the 
> > | database, and they can slow down updates and inserts a little.  But 
> > | in the average case, the user doesn't know the difference, and 
> > | they're a lot more likely to complain about the vast slowness of a 
> > | full table scan due to a missing index than they are likely to 
> > | complain about inserts being 4% slower or whatever.
> > |
> > | > could it be that some databases generate indexes for FK's
> > | by default
> > | > and that the explicit index results in a second index being
> > | generated
> > | > and maintained ?
> > |
> > | No database I know of will create indexes by default for FKs, but 
> > | that doesn't rule it out I suppose.  I'd think that the risk is low 
> > | though.
> > | Even if it were the case, in my opinion it is up to Hibernate to 
> > | recognize the existing indexes and not generate DDL for them.
> > |
> > | My .02c.
> > |
> > | - DML
> > |
> > |

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira