What to Index?

[tara99]

Does indexing need to be done only for primary key or for any other field too.

can some one please clarify?
thanks

[Killer42]

Does indexing need to be done only for primary key or for any other field too.
can some one please clarify?
thanks

Generally speaking, you'd probably want to index any fields that you need to search on.

[NeoPa]

Indexing is a bit of a balancing act.
Every time you get data from a table, that data will be returned more quickly if Access has a relevant index it can use.
Sometimes an index can only do part of the sorting job, if so it will still be much faster than not having an index at all.
On the other hand, every time you write any record, extra work must be carried out for each index inserted into.
As a general rule, a record is accessed more times with reads than it is with the (generally single) write.
This normally means that it's better to add indices where they will be used by your queries.
You can't really have proper rules for this as there are so many factors that it depends on.

[tara99]

Indexing is a bit of a balancing act.
Every time you get data from a table, that data will be returned more quickly if Access has a relevant index it can use.
Sometimes an index can only do part of the sorting job, if so it will still be much faster than not having an index at all.
On the other hand, every time you write any record, extra work must be carried out for each index inserted into.
As a general rule, a record is accessed more times with reads than it is with the (generally single) write.
This normally means that it's better to add indices where they will be used by your queries.
You can't really have proper rules for this as there are so many factors that it depends on.

Thanks Guys

[Killer42]

Thanks Guys

One more thing - unless I'm mistaken, any field used in a JOIN probably should be indexed. (That is, the fields you're actually JOINing on, not everything retrieved).

[NeoPa]

Ideally - yes.
Sometimes there are too many different types of queries on a table to let that be practical.
It's certainly an important consideration though.

[PEB]

Not necessary to be indexed the fields that participate into the Join but if they are it should work faster

But if you have to do multiple Insert Into your table.../Append queries/ Forgot for speed if you have more than 3 -4 indexes... The max number of indexes that can permit you fast functionning of search and write is 3-4 per table...

Once i've done 10-15 and 20000 records were inserted for 1 hour and more... It's horrible in those cases...

Other method using indexes... when you need to populate a big quantity of data in your table you remove all indexes, u append your data and than you recreate the indexes...

[nico5038]

Indexing is a way to improve the speed of the Jet engine (database engine).
For detailed information you can download and check:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q162701

The unsupported feature "showplan" is effectively showing or defined indexes are used for a query.
An index will be used only when there are only few records that satisfy the WHERE condition. For the Jet engine using an index does not only speed up processing, but also can slow it down as for an index a separate index file is created and the reading of that is an extra burdon. With a hitrate of e.g. 50% it's faster to scan the complete table as taking the extra effort to read the index.

Success !

Nic;o)

[PEB]

However choosing the appropriate index saved me 3 seconds from 7 seconds time of ececution!

[Killer42]

...But if you have to do multiple Insert Into your table.../Append queries/ Forgot for speed if you have more than 3 -4 indexes... The max number of indexes that can permit you fast functionning of search and write is 3-4 per table...

Once i've done 10-15 and 20000 records were inserted for 1 hour and more... It's horrible in those cases...

Cases do vary quite considerably, though. With my database of, say, 5 to 10 million records (it varies over time), each day I do an import of about 40,000 to 50,000 new records. I have 6 indexes, including one which is a compound of a couple of fields. The import typically takes a minute or two.

There are so many variables, I think the most you can say is that when creating/updating records, more indexes = slower performance. I wouldn't try to set a specific cutoff point.