[RESOLVED] Indexing?

[Quizton]

Hello ,
My question is reguarding VB6.0 & MSAccess Visual data manager.
I have recently been toyin around with this vb6 and was wondering when should I index my tables?, and also, What does it benefit? kinda just the ups and downs? I would also like to thank all of you guys/gals, helping me out, I realize these are the bottom of the totem poll questions. I have an idea of what it is from reading my book, and lookin online, but you seem to know what you are doin and I always like hearin about a few examples from the "Real Deal"

[abhijit]

it is quite simple with regards to indexes, follow these two rules:

1) have as few indexes as possible
2) but as many as you need

[szlamany]

Each index increases the amount of time needed to INSERT new records - let's assume that the first index doubles the time for INSERT...

So you can see where abhijit is coming from with the suggestion to do as few as possible.

But indexes incredibily speed up access to data when the WHERE clause is related to columns of the index.

We use MS SQL SERVER and always have at least one primary index (almost always CLUSTERED) for each table so that a unique identifier is available for every row.

[Hack]

it is quite simple with regards to indexes, follow these two rules:

1) have as few indexes as possible
2) but as many as you need

This is very cool way of putting it.

[Quizton]

Thanks again All, I think I get the picture now =)

[abhijit]

This is very cool way of putting it.

Thats from a text-book I once read a long time ago. I am not sure who came up with that gem.