• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
  • Mikalai Zaikin

when to index column(s)

Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
May somebody illuminate me of this thing?
+ and - es? under which conditions?
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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, a.name WHERE a.id='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
    Bookmark Topic Watch Topic
  • New Topic