WindSailor
Registered User.
- Local time
- Yesterday, 16:21
- Joined
- Oct 29, 2003
- Messages
- 239
Non Clustered Indexes
There is not a whole lot of information on creating / using Indexes for Access.
I believe Access does not have clustered indexes (A clustered index physically orders the data in a table based on a single or composite column - I don’t think ‘Autonumber’ would apply) and when building my tables trying to abide by the ‘Normal Forms’ guidelines I have always put the most queried column first, second column next etc. and basically used indexes in the same manner.
Now doing some digging on ‘indexes’, I found the following suggestion regarding ‘non clustered’ indexes:
“Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.”
Interesting… because originally I thought that would be backwards, evidently not true.
Here are some tips regarding indexes, but these are geared for MS SQL Server, not Access (although I would apply whatever I could) from here:
http://www.mssqlcity.com/Articles/Tuning/IndexOptimTips.htm
• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you want to join several tables, try to create surrogate (substitute or replacement) integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
*A covering index is an index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.
Can anyone expand on this?
Edit----
And would taking your database table structure to a higher normal form (less indexes per table) increase the speed, or does the cumulative indexes count as a whole (I am assuming this is true)?
Edit----
I changed the title to 'Non Clustered Indexes' because I thought Indexes should be split into two different catagories: Clustered and Non Clustered, I also thought it would change the title of this thread in this forum, it didn't, sorry.
Thanks
There is not a whole lot of information on creating / using Indexes for Access.
I believe Access does not have clustered indexes (A clustered index physically orders the data in a table based on a single or composite column - I don’t think ‘Autonumber’ would apply) and when building my tables trying to abide by the ‘Normal Forms’ guidelines I have always put the most queried column first, second column next etc. and basically used indexes in the same manner.
Now doing some digging on ‘indexes’, I found the following suggestion regarding ‘non clustered’ indexes:
“Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.”
Interesting… because originally I thought that would be backwards, evidently not true.
Here are some tips regarding indexes, but these are geared for MS SQL Server, not Access (although I would apply whatever I could) from here:
http://www.mssqlcity.com/Articles/Tuning/IndexOptimTips.htm
• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you want to join several tables, try to create surrogate (substitute or replacement) integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
*A covering index is an index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.
Can anyone expand on this?
Edit----
And would taking your database table structure to a higher normal form (less indexes per table) increase the speed, or does the cumulative indexes count as a whole (I am assuming this is true)?
Edit----
I changed the title to 'Non Clustered Indexes' because I thought Indexes should be split into two different catagories: Clustered and Non Clustered, I also thought it would change the title of this thread in this forum, it didn't, sorry.
Thanks
Last edited: