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 when to index column(s) 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 "when to index column(s)" Watch "when to index column(s)" New topic

when to index column(s)

Brusk Baran
Ranch Hand

Joined: Nov 15, 2001
Posts: 132
May somebody illuminate me of this thing?
+ and - es? under which conditions?
Surendran Velath

Joined: Apr 23, 2003
Posts: 19
Here is a brief note that I had prepared some time back. Hope it will be useful:
Need for Indexes
An index placed on a table helps to retrieve data faster.
A well placed index makes the application run faster when accessing the data.
This is possible because the Database engine looks at the index instead of the column when a select statement is fired on that column.
The buffer can hold more number of rows since the db engine does not go through all the columns of the table.
If more than one column is to be selected, then concatenated indexes are used. Indexes are sorted and can be unique.
Indexing is used in case of a search within the database
The following are the rules for indexing:
� The columns mentioned in SQL statement predicates ( the 'where' or 'and' selections ) should be indexed
for example : SELECT a.age, WHERE'P991';
If column 'id' was indexed, the DB engine looks for id='P991' in the index which is ordered and quickly finds the location through the rowid in the corresponding table. Else search has to be made from start to 'P991' till the record is found.
� If a column has a large number of distinct values (not repeated) it is a candidate for indexing.
� If multiple columns are continually referenced together in SQL statement predicates, these columns should be put together in a composite index.
� The section under IN, NOT IN, EXISTS are not indexed.
� The columns in the join condition of two tables are indexed.
-- Surendran
I agree. Here's the link:
subject: when to index column(s)
It's not a secret anymore!