If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
index when to add
hello all,
my understanding is that an index should be added to fields that are often searched or sorted, and can be added to individual fields, or to a group of fields. if fields a, b, c, & d are typically sorted and used in joins, should i add an index to field a, field b, field c, field d, fields a & b & c & d, fields a & b & c, fields a & c & d etc etc? is there a disadvantage to adding too many indexes? what is the general rule as to whether or not an index should be added? is there any harm in deleting an index after it has already been added? thanks! |
#2
|
|||
|
|||
index when to add
Question 1: Add an index for each field. Not for the combinations of the fields.
Question 2: Yes there is a disadvantage. Each index you add will slightly slow down saving records as the index has to be updated in addition to the record being saved. Also, there is some storage space used for the index. And there is a limit on the number of indexes (32 maximum per table). Every relationship will use one of the indexes for the table, but the index is hidden from your view. So, you will usually not need to add indexes for fields that are involved in a defined relationship. Question 3: GUIDELINES ONLY. Add an index when you frequently sort or filter based on the field. If the table is really small, an index may not be helpful. If the data stored has little differentiation (e.g., True/false field that has roughly half true and half false values) then an index may be of little benefit. If sorts and filters are slow and you don't have an index on the field - add one and see if that helps. Question 4: Deleting an index does not harm to your data. It may affect the speed of sorts and filtering records. If it does, you can always add the index back (assuming you have not reached the 32 index limit). John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County joemeshuggah wrote: hello all, my understanding is that an index should be added to fields that are often searched or sorted, and can be added to individual fields, or to a group of fields. if fields a, b, c, & d are typically sorted and used in joins, should i add an index to field a, field b, field c, field d, fields a & b & c & d, fields a & b & c, fields a & c & d etc etc? is there a disadvantage to adding too many indexes? what is the general rule as to whether or not an index should be added? is there any harm in deleting an index after it has already been added? thanks! |
#3
|
|||
|
|||
index when to add
Access can only have 32 indexes per table, I believe. Therefore there is a
'too many indexes' limit. Indexes are like little tables. Multiple indexes can take up more space than the original table. Indexes may speed up searches; however, they will slow down inserts, updates, and maybe even deletions. Pick your poisen. Using your example with all the indexes: When Access runs the query, it will probably only pick one of the indexes to use and the others will just take up space. if fields a, b, c, & d are typically sorted and used in joins If that is the norm, I'd create a composite index that contains all 4 fields. As an added bonus, it would also use such an index if you had criteria just on A (but probably not on b, c, or d). Joins are the biggest user of indexes especially if joining by the Primary Key field. Criteria is the next biggest user of indexes. A distant third is sorting. If you have field(s) designated as a PK, it already has an index. In fact Access has a bad habit of creating 2 indexes if your PK field name has ID; key; code; or num in it such as PersonID. Delete the second such index. As far as criteria, add an index based on that field and get out a stopwatch. See if makes a noticable improvement. If not, drop the index. Of course if you add 10K more records, it might change in the future. If you really want to know what indexes are used by which queries, check out Showplan: http://articles.techrepublic.com.com...1-5064388.html -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "joemeshuggah" wrote: hello all, my understanding is that an index should be added to fields that are often searched or sorted, and can be added to individual fields, or to a group of fields. if fields a, b, c, & d are typically sorted and used in joins, should i add an index to field a, field b, field c, field d, fields a & b & c & d, fields a & b & c, fields a & c & d etc etc? is there a disadvantage to adding too many indexes? what is the general rule as to whether or not an index should be added? is there any harm in deleting an index after it has already been added? thanks! |
#4
|
|||
|
|||
index when to add
If you relate one table to second table on field A, and to a third table on
field B, then you need separate indexes on A and B. You will need a compound index on multiple fields only if multiple fields are involved in *one* relation. If you do need a compound index on fields A&B, then adding one on A alone is wholly redundant. Example: think of a white pages phone book, which is indexed on last name & first name. You don't need a separate index on last name alone to find instances of "Smith". -TedMi "Jerry Whittle" wrote in message ... Access can only have 32 indexes per table, I believe. Therefore there is a 'too many indexes' limit. Indexes are like little tables. Multiple indexes can take up more space than the original table. Indexes may speed up searches; however, they will slow down inserts, updates, and maybe even deletions. Pick your poisen. Using your example with all the indexes: When Access runs the query, it will probably only pick one of the indexes to use and the others will just take up space. if fields a, b, c, & d are typically sorted and used in joins If that is the norm, I'd create a composite index that contains all 4 fields. As an added bonus, it would also use such an index if you had criteria just on A (but probably not on b, c, or d). Joins are the biggest user of indexes especially if joining by the Primary Key field. Criteria is the next biggest user of indexes. A distant third is sorting. If you have field(s) designated as a PK, it already has an index. In fact Access has a bad habit of creating 2 indexes if your PK field name has ID; key; code; or num in it such as PersonID. Delete the second such index. As far as criteria, add an index based on that field and get out a stopwatch. See if makes a noticable improvement. If not, drop the index. Of course if you add 10K more records, it might change in the future. If you really want to know what indexes are used by which queries, check out Showplan: http://articles.techrepublic.com.com...1-5064388.html -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "joemeshuggah" wrote: hello all, my understanding is that an index should be added to fields that are often searched or sorted, and can be added to individual fields, or to a group of fields. if fields a, b, c, & d are typically sorted and used in joins, should i add an index to field a, field b, field c, field d, fields a & b & c & d, fields a & b & c, fields a & c & d etc etc? is there a disadvantage to adding too many indexes? what is the general rule as to whether or not an index should be added? is there any harm in deleting an index after it has already been added? thanks! |
#5
|
|||
|
|||
index when to add
But don't create indexes on foreign keys if your relationships are enforced
(as they should be). Access automatically creates hidden indexes on foreign keys, so if you create explicit indexes they will just be duplicates that waste space and waste time being updated when data is edited. Continuing TedMi's phonebook example, the index on {lastname, firstname} will help a lot for a) sorting by lastname or sorting by lastname and then firstname and b) finding someone by lastname and c) finding someone by lastname and firstname, but cannot be used for sorting by firstname or finding someone by firstname. For an index to be useful in sorting, the columns have to be included in the index in the order they are used for sorting. For an index to be useful in search, the search must at least specify a criterion for the first column in the index. The table's row count matters too. A table with a few thousand rows probably won't see much if any performance improvement from indexes. Tables with 100,000 rows probably will. It might be worth identifying the specific queries with performance issues and then considering indexes that could improve those particular queries. "TedMi" wrote in message ... If you relate one table to second table on field A, and to a third table on field B, then you need separate indexes on A and B. You will need a compound index on multiple fields only if multiple fields are involved in *one* relation. If you do need a compound index on fields A&B, then adding one on A alone is wholly redundant. Example: think of a white pages phone book, which is indexed on last name & first name. You don't need a separate index on last name alone to find instances of "Smith". -TedMi "Jerry Whittle" wrote in message ... Access can only have 32 indexes per table, I believe. Therefore there is a 'too many indexes' limit. Indexes are like little tables. Multiple indexes can take up more space than the original table. Indexes may speed up searches; however, they will slow down inserts, updates, and maybe even deletions. Pick your poisen. Using your example with all the indexes: When Access runs the query, it will probably only pick one of the indexes to use and the others will just take up space. if fields a, b, c, & d are typically sorted and used in joins If that is the norm, I'd create a composite index that contains all 4 fields. As an added bonus, it would also use such an index if you had criteria just on A (but probably not on b, c, or d). Joins are the biggest user of indexes especially if joining by the Primary Key field. Criteria is the next biggest user of indexes. A distant third is sorting. If you have field(s) designated as a PK, it already has an index. In fact Access has a bad habit of creating 2 indexes if your PK field name has ID; key; code; or num in it such as PersonID. Delete the second such index. As far as criteria, add an index based on that field and get out a stopwatch. See if makes a noticable improvement. If not, drop the index. Of course if you add 10K more records, it might change in the future. If you really want to know what indexes are used by which queries, check out Showplan: http://articles.techrepublic.com.com...1-5064388.html -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "joemeshuggah" wrote: hello all, my understanding is that an index should be added to fields that are often searched or sorted, and can be added to individual fields, or to a group of fields. if fields a, b, c, & d are typically sorted and used in joins, should i add an index to field a, field b, field c, field d, fields a & b & c & d, fields a & b & c, fields a & c & d etc etc? is there a disadvantage to adding too many indexes? what is the general rule as to whether or not an index should be added? is there any harm in deleting an index after it has already been added? thanks! |
Thread Tools | |
Display Modes | |
|
|