Solved

Multiple Index on multiple fields vs one index with multliple fields

Posted on 2011-02-17
11
355 Views
Last Modified: 2012-05-11
In Access I have a table where multiple queries search based on criteria in multiple fields within the same table.  What's the difference between creating one Index that includes each of the 3 fields searched vs. a separate index for each field within the same table that will be searched?

What would be the criteria for selecting one method over the other?  Are there advantages / disadvantages  to one approach vs. the other?

I noticed when I use the design view of the table and I select the Index option for 3 of the fields within the table and hit save 3 separate indexes are created.  If I used SQL to create an index that includes the 3 fields then it's one index with the 3 fields (kind of obvious I guess... but I'm just trying to understand indexes better).

Thanks!

 

0
Comment
Question by:ClayCanvas
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 100 total points
ID: 34921003
If you will always be searching by all the field then a compound index would be better.

I create indexes based on searching and linking needs.

It all depend on the application and how the database is use with way is best.




0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34921079
0
 
LVL 21
ID: 34921094
I noticed when I use the design view of the table and I select the Index option for 3 of the fields within the table and hit save 3 separate indexes are created.

That sound like you are creating three separate indexes not a compound index.







CoumpoundIndex.png
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 34921122
<<What would be the criteria for selecting one method over the other?  Are there advantages / disadvantages  to one approach vs. the other?>>

  A compound index will only be used if your criteria is applied in the exact same order.

 In general, your better off with three seperate indexes and let the query develop a costing plan that works the best.

JimD.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 34921151

 BTW, you might be interested in JET's SHOWPLAN:

http://msdn.microsoft.com/en-us/library/dd942824(v=office.12).aspx#odc_ac2007_ta_PerformanceTipsToSpeedUpYourAccessDB_JetEngineTuningTips

  Look under the JET tuning tips.

JimD.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 44

Expert Comment

by:GRayL
ID: 34921175
This is a rather complex issue.  It would be a good idea to Google - compound indexes access - and read the first three or four hits.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 150 total points
ID: 34921311
As I have found over the years, working with Indexes is an Art Form more than not ... experimentation ... trial and error, testing one way vs another.  And as data sets increase in size, indexes may have to be adjusted.

Here are some sources with a lot of good information.  I doubt if there is any once 'best' source, as there is much conflicting information on the subject.  Doing one's own R&D on the subject (read: testing) is probably the most prudent approach.

http://support.microsoft.com/kb/172199

http://www.fmsinc.com/tpapers/faster/index.html

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx

Note that indexes can be a double edge sword. Whereas they will (usually) most definitely speed up searches and filtering - almost like magic sometimes,  each time you add or delete a record, all indexes in the table have to be updated by Jet. So, if you have several hundred thousand records, then the updating time could be significant, depending on various aspects of your system and/or network connection is there is one, etc.

mx
0
 

Author Closing Comment

by:ClayCanvas
ID: 34921438
Wow... thanks for all of the feedback... after reading your comments and the different links to other pages I think I have a much better understanding now of indexes.

Thanks Again!
0
 
LVL 57
ID: 34921446
Ray,

<<This is a rather complex issue.  It would be a good idea to Google - compound indexes access - and read the first three or four hits. >>

  Do you really think that is a good comment?  I have to ask, why did you bother?  I really can't believe you left it at that.

JimD
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34921929
Well Jim, I did spend about 20 minutes reviewing the hits I got after the Google post and I concluded it was exactly as I had posted - complex!  Are you having a bad day?
0
 
LVL 57
ID: 34922935
<<Well Jim, I did spend about 20 minutes reviewing the hits I got after the Google post and I concluded it was exactly as I had posted - complex>>

  Yeah, but I know you better then that!  I guess I was more surprised rather then anything to see a comment of "go search the internet" from you.

  It seemed to me the focus of the question was more on how Access handled indexes and query optimization, so I should also add that I was re-miss in not commenting on Caps comment as well.  He simply has two links on indexing with a "see these links" as the only comment and nothing specific.  Sometimes a link may be appropriate, but in this case I don’t think so as I don't think either link really addresses the question.

<<Are you having a bad day? >>

   For the most part no, but to be honest, I did post that after coming off of one piece of EE business that did leave me feeling a bit frustrated with an overall lack of progress, which can now be measured in years.

  But I should also mention that part of the impetus behind that comment was that once again ZA's site wide are being encouraged to make sure comments are made with some meat behind them.  Too many questions are getting comments with a “see this” and a couple of links and most of the time, that’s simply not enough nor appropriate.

  In fact some ZA's are going as far as simply deleting those types of comments.  I don’t like being that heavy handed, and since the MS Access Zone is one of the better ones on the site, I really don’t have to be when it comes to the ZA duties (well most of the time anyway<g>).   That’s why I didn't post as an admin comment, but rather as an Expert.  That comment however may have come off sharper then I intended though.

  I also believe it should be the asker that has the final say in what works for them and what doesn’t, but we’ve been seeing an up tick in complaints about getting nothing but links in a response to a question.

  This may not have been the proper approach though.  In fact the more I think about it, it was not, so sorry for that.  What I really need to do is post something in the alert thread stating the above so all are aware moving forward.

JimD.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now