Multiple Index on multiple fields vs one index with multliple fields
Posted on 2011-02-17
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).