Why Index?

[Andy]

I am using a table with almost a million records and redesigning a database
to run a series of queries which currently are very lengthily. Could someone
recommend where I could read up on indexing tables etc to help things run
more quickly?

Thanks

 

[Duane Hookom]

I generally index fields that are:
- used in joins
- used in criteria
- used in Order By clauses

A quick Google found Garry Robinson's page "Indexing Tables Tips For
Microsoft Access" http://www.vb123.com/toolshed/99/indexfields.htm.

 

[John W. Vinson]

I am using a table with almost a million records and redesigning a database
to run a series of queries which currently are very lengthily. Could someone
recommend where I could read up on indexing tables etc to help things run
more quickly?

Thanks

Good indexing is critical and can make very dramatic improvements in
performance... but bad indexing can make things WORSE, especially for update
and insert queries. It's both a skill and an art, and can require some
thorough testing.

In addition to Duane's recommendation I'd take a look at Tony Toews' site
http://www.granite.ab.ca/access/performancefaq.htm. It's not specifically
about indexing but contains a lot of more general performance tips.

 

[Jerry Whittle]

Just because you create an index, it doesn't mean that Access will use it.
There's a Jet tool called ShowPlan which can show you how a query is run
including what indexes are used.

Showplan
http://articles.techrepublic.com.com/5100-10878_11-5064388.html

Besides indexing, sometimes it's just the way the query is written. For
example if I write an In/Not In clause and it's slow, I'll modify it into an
Exists/Not Exists or visa versa. Sometimes the difference is dramatic.