Use of Index in Ms-Access Database

-2

Why does an Index make a query run faster? What does an index do? Particularly in Access, if you declare a column as an index how does it affect the query?

Any Ideas?

Coding Enthusiast

Posted 2015-06-05T14:36:50.090

Reputation: 163

You have a SO account, and this has been answered on SO http://stackoverflow.com/questions/1108/how-does-database-indexing-work

– Dave – 2015-06-05T14:42:10.770

@Dave due to the fact that questions like this one are no more allowed on stackoverflow, I decided to ask it here and i tried googling it and didn't really find much on ms-access. I didn't check SO because I wouldn't imagine it to be there – Coding Enthusiast – 2015-06-05T14:46:54.143

Yup, it's not always clear where we can/can't post but hopefully the answer there is sufficient. It's not focused MS Access but, the principal is the same – Dave – 2015-06-05T14:50:56.490

If you have a computer software manual of 1000 pages and want to look up a topic on a specific thing, what do you do? Go to the index, and it will tell you the page number. This is similar to what happens in a table. – Saulysw – 2015-07-09T09:39:39.530

Answers

This is very complicated stuff, so I'll give you the summary.

Data is stored on disk inside a file, so each record in a table in the file has a physical storage address at which it starts.

Indexes store the indexed field, and the storage address the record occurs at, in a tree datastructure that is optimized for search (often a B-Tree), such that it takes very few comparisions to navigate the datastructure, find the desired indexed data, and then retrieve the storage address at which the record is stored. The record can then itself be retrieved.

So, instead of reading the table row by row to find the record, we ask the index. the index makes 3 reads through the B-Tree, and finds indexed item being sought, with the address of the data tied to the item, and then returns the address. If your record was the 1024th row in the table, you have just accomplished the task in 4 reads, instead of 1025.

Frank Thomas

Posted 2015-06-05T14:36:50.090

Reputation: 29 039

From this Microsoft article

Create and use an index to improve performance

You can use an index to help Microsoft Office Access find and sort records faster. An index stores the location of records based on the field or fields that you choose to index. After Access obtains the location from the index, it can then retrieve the data by moving directly to the correct location. In this way, using an index can be considerably faster than scanning through all of the records to find the data.

wbeard52

Posted 2015-06-05T14:36:50.090

Reputation: 3 149