Solved

Multiple Index on multiple fields vs one index with multliple fields

Posted on 2011-02-17
11
366 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
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

 
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
 
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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

15 Experts available now in Live!

Get 1:1 Help Now