Non-indexed foreign keys

Let me start my first accepting a misconception which I had till very recently. I sort of always assumed that foreign keys are always indexed. A little more digging revealed that Oracle, DB2 and even SQL server does not create an index for a foreign key automatically. So it has to be explicitly done.
We all know indexes has cost associated to it. Apart from using the storage space, it has to be maintained by the database on inserts and updates but still it is very good idea to create an index for a foreign key and especially when

• We have a use case where deletion of the parent row cascades into deletion on the child table. Otherwise it will lead to a full table scan of the child table for each parent row deletion.

• We frequently join parent and child tables. Otherwise it will have a performance impact.

So basically a little bit of a trade off as what we really want to do but in general it is always a good idea to have an index on a foreign key.

But does all this have any impact on how we do our Hibernate mappings, yes for sure. So for example if we decide to not index a foreign key, then

• Be careful while using : on-delete=”cascade” even though the database has a support for it

• Always specify lazy=”true” to prevent a join and eager fetch of the child rows.

It’s time for your database to perform

The number one reason for applications not performing the way they can is the database misuse. May be the following few simple steps can go a long way making our J2EE applications perform and scale well

1. Use databases for only that data which you need to persist and not for everything.

2. Model and design your database to third normal form in general but do the de-normalization of the database to cater to specific functional needs

3. Create indexes where necessary but lets not overdo it as it has its own impact while inserting stuff as database might take up lot of time updating them.

4. Learn about the specific datatypes our database offers and use them to maximum as some might perform better then the others