indexing

Which one gives better performance * Indexing on Primary Key * Indexing on Foreign Key * Indexing on non-keys ???

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.