Solved

Multiple Index on multiple fields vs one index with multliple fields

Posted on 2011-02-17
11
345 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
Comment Utility
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
Comment Utility
0
 
LVL 21
Comment Utility
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
Comment Utility
<<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
Comment Utility

 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

8 Experts available now in Live!

Get 1:1 Help Now