Thread: Deciding what to Index
02-08-06, 06:25 #1Registered User
- Join Date
- Feb 2006
Unanswered: Deciding what to Index
Just trying to decide what extra indexes to create for tables in my Access Database.
After reading the help files it says to generally index by fields you sort by, search by or use in a join query. Also generally indexes are only useful if most of the data is mostly unique.
There is obviously a tradeoff between search speed and speed of updates to data in the table. How many indexes should you generally stick to in a table? Too many will just make updating the table very slow.
The help file also mention that indexes can cause problems when the database is used by multiple users in that it reduces concurrency (the ability of more than one user to modify a page at the same time) and may cause locking problems. Has anyone had any experiences of indexing causing problems when multiple users are using the Access Database? Is it better to keep indexing to a minimum if the database is used by multiple users?
Thanks for any help.
02-08-06, 07:18 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Do you have some justification in believing that the help file / manual is lying to you?
keeping indexes to a minimum is a smart moce - in any event it reduces the amount of work that the databse engine has to do (on writing and reading and updating)
using indexes only when required is a smart thing to do
allowing the db engine to select which indexes to use when running is a smart thing to do.
There is no easy option, as a general rule of thumb index those columns which are going to be used in "where" clauses (or at least those used in significant where clauses (forms reports etc). If a date column isn't used for reporting / extraction purposed then don't index it, if you plan on querying the underlying data based on dates then index it.
Concurrency issues overe indexes will dissappear if you switch to a server datastore - indeed its one of the reasons why a server model is more stable ofr multi users.
As regards to sepcific problems arising out of indexes in realtionm to using JET in a multi user environment. I have never had such problmes, however I do have (and have had) problems with using JET in a multi user environment).
Again the problem is not Access - Access will run very happily with thousands of concurrent users, but NOT Access using JET as the database engine - that chokes anywhere above 20..50 users, sometimes more, sometime less.I'd rather be riding on the Tiger 800 or the Norton
02-08-06, 13:02 #3King of Understatement
- Join Date
- Feb 2004
- One Flump in One Place
Aah - indexing!
Some hold that this is more an art than a science. Specifically - there is no one size fits all rule - you just need to think it through and test when appropriate.
For example- the notion that indexes lead to slower updates doesn't necessarily hold. Imagine the favourite analogy for this topic - a book with an index. If you decide to rewrite one of the paragraphs in the book but, crucially, the paragraph is still about the same topic it always was (i.e. the index does not need alter to reflect the changes in the paragraph because the paragraph is still broadly about the same thing) then the index actually speeds up updating. This is because the index helps you locate the paragraph you want to update yet you don't need to make any changes to the index once done. Without an index, you need to scan through the entire book to find the paragraph before you can change it. The same holds true for JET. If changing the paragraph means it is now about a differnet topic then the index does create an overhead as you have to change this too on order to match the data.
The broad rule still applies (index read only data like mental, be more judicious with read\ write data) - just think your ideas through. I agree with Mark though - keep them to a minimum and then add as you need and test. If you start out with loads and find you are having problems then it will be hard to find the offending index.
There is more that you need to consider. For example, you quoted selectivity - as such you can infer indexes on true\ false columns are a bit pointless.
ur codings are working excelent.