Can Indexes slow queries down?

[SamirNaenenjad]

I have a large Access 97 database (~500MB) that has a large table with about 250,000 records. This table has a lot of different indexes. There is no primary key set on the table, but many queries I use do a join on fields A and B (both text fields and indexed) as if they were a composite key.

These queries used to run fine, until I added some more indexes to some other fields people wanted to search on. All of the sudden, any query that used a join on field A grinded to halt. After much experimentation, I isolated the problem and determined that taking the index off of field A made the queries run quickly again.

This surprises me because I was taught that indexes sped up searches and joins on the indexed fields (but added to the size of the DB). Has anyone else seen behavior like this? Does anyone have a theory as to why this may occurr?

Thanks,
A.J.

 

[psemianonymous]

If you have the time (maybe at the end of the day?) re-add the index and/or compact the database. I was reading lots of newsgroup threads, and they say that the only thing indexes slow are update/insert queries. SELECTs are apparently speeded up in all cases.


threads I've been browsing:

http://groups.google.com/groups?hl=...y+indexes&meta=group=comp.databases.ms-access

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[SamirNaenenjad]

These queries were INSERTS and UPDATES, so I can see now how indexing could slow things down. The difference in performance is incredible, though. A typical query goes from taking 30 seconds to taking 30 minutes with no apparent progress, then I kill it.

Another interesting item is that Field A is the only one that causes problems, not Field B. In this case, A is actually an animal "Breed", while B is an animal "ID". IDs are unique within the breed, so there are very few duplicate IDs. There are hundreds of thousands of duplicate Breeds. This led my colegue and I to speculate that perhaps the fact that a particular Breed is duplicated so often, this creates an enourmous amount of work to create the index during the INSER/UPDATE. I don't know much about how indexes are implemented in Access - perhaps someone can shed some light on this.

Anyway, thanks for the info - it got our minds out of a rut.

-A.J.