When & where 2 create an index?

[Julian Milano]

I have an Access db which is queried from an XL application. I've looked at
the db and it has NO indexes. I presume that indexing the tables/fields in
the db will make the queries run faster, but how do I know which
table/fields to create indexes in?

I do have access to the SQLs in the XL application. Do I create an index for
every field that appears in the "SELECT fldField1, fldField2,...." line?

 

[John Vinson]

I do have access to the SQLs in the XL application. Do I create an index for
every field that appears in the "SELECT fldField1, fldField2,...." line?

Probably NOT. However, any field which occurs in a WHERE expression or
an ORDER BY expression should probably be indexed.

The WHERE clause specifies which fields will be searched for data - a
process which is greatly helped by indexing, since a given field value
will be stored once only in the index and will supply pointers to all
records containing that value; without the index, Access must retrieve
every single row in the table one by one and check its value. Sorting
(the ORDER BY) clause also benefits greatly from indexing. Once a
record is found (and put into its correct order), SELECTing a field
value does not need or use an index.