Someone mentioned to me that the speed pf a big query will be improved if I include the primary key index field in the query. Just I am wondering if any of the people that program lots know if this is right.
Thank you.
Thank you.
Someone mentioned to me that the speed pf a big query will be improved if I include the primary key index field in the query. Just I am wondering if any of the people that program lots know if this is right.
Thank you.
Thanks I think I get it. So that means I have to look at the tables and make the indexes on the fields in the tables, they can have more then just primary, secondary, tertiary? I do not want to index all the fields but it is possible to have more then just a few fields indexed?
Will the extra indexes impact the performance of the database, not going overboard with indexing but if there is more then a couple to each table?
Thank You
It is a common myth that every table should have a key field.
A key field is only essential if you need to refer to the individual records from other tables.
Adding a Primary Key field creates little or no overhead
I don't believe so.Would an index speed up a select query with no criteria?
Where I would imagine an impact is on an insert or update as the db engine would have to reindex thus having a large number of indexes surely would impact update, insert and delete. Even then does it impact on the time taken to run the query or rather the processing after the query has been executed?
The index on a key field is not about speed but about avoiding duplictes. Using an unnecessary key just bogs down the processing for no gain.
This is really where access blurs the lines somewhat. The difference between an index and a constraint.
However in the case of a Key field I would argue that this would also effect speed as an update and delete query would have to find the record first. However this is just about the only example worth mentioning...
Indexed no Duplicates slowing down a update query lends itself to the arguement for serial or autonumber Keys as the lookup is then no longer required. The question again is architecture of the engine, does it do the lookup anyway even though it has itself produced the number. The answer to this is Yes so therefore it voids any logical saving.
You also state that the use of an unnessesary key would bog down the processing, would the engine not be doing that in a completely seperate thread therefore (as long as you dont cause 50% cpu usage) the data access time would/should not be effected?
The field I want to use as a key index is a 13 character number. This is a unique number related to batch numbers being run in manufacturing based on a compilation of work order number, workorder line number and a sequence number.
If it uses a 1/2 principal to index will this cause a speed issue or is it same as if I used the autonumber key index that Access will supply?
How is the field you want a idex defined. Is it a long integer or is it a text field? This will have a big effect on its effciency as an indexThe field I want to use as a key index is a 13 character number. This is a unique number related to batch numbers being run in manufacturing based on a compilation of work order number, workorder line number and a sequence number.
If it uses a 1/2 principal to index will this cause a speed issue or is it same as if I used the autonumber key index that Access will supply?
I'm not an indexing Expert by any means, so I use posts like this:
http://www.access-programmers.co.uk/forums/showpost.php?p=886247&postcount=5
http://www.access-programmers.co.uk/forums/showpost.php?p=761425&postcount=2
etc.
How is the field you want a idex defined. Is it a long integer or is it a text field? This will have a big effect on its effciency as an index
Basically we discussing things on a very theoretical level here - as the index can logically sort it will speed things up alot. Dont get fooled by all out bantering the index is a good thing as long as its used in moderation and logically