add indexes to foreign keys

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
> |
> |