File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes indexing Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "indexing" Watch "indexing" New topic


kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1430
Which one gives better performance * Indexing on Primary Key * Indexing on Foreign Key * Indexing on non-keys ???
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
As with most things concerning performance, it depends on your needs and use cases.

First, primary keys are always indexed (at least in Oracle, and likely most if not all relational DBs). The reason is that you're almost always going to query by PK.

You only need to index a foreign key if you need to query by it (find all bids for a given item -- bid has item_id FK). However, if the table with the FK contains a small number of rows, the overhead of maintaining the index may outweigh any savings from using it for queries. And of course, if your queries are already narrowing down the result set before looking at the FK, the query plan may skip the index altogether and just look at each row.

If you're not going to query, sort or enforce uniqueness on a set of columns, there's really no reason to index it (am I missing another case for indexing?).

You need to measure the performance of your application and then decide how to tune it. There is no single catch-all solution to performance tuning.
I agree. Here's the link:
subject: indexing
It's not a secret anymore!