CODEWITHSUNDEEP
vb.netms-access
Sameer
Sameer

Reputation: 33

To create index on a varchar column or not - Access

I have read a couple of articles about when to create an index on a column and all of those were related to Mysql, SQL Server or Oracle. I have a fair bit of idea now about whether I should create an index on my column or not, but I would like to have a learned opinion on it before I actually try it.

I have a MS Access database which has around 15 tables. All tables have a column called [Locations] and this column is used in almost all WHERE clauses and most of the JOIN conditions. This column has 5 distinct values as of now i.e 5 locations viz A, B, C, D, E.

So my question is though this column is part of most WHERE clause and JOIN, the limited variety in values (just 5) is making me to hesitate to create an index on it.

Please advice.

Upvotes: 1

Views: 328

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

It is important to bear in mind that an Access database is a "peer-to-peer" (as opposed to "client-server") database, so table scans can be particularly detrimental to performance, especially if the back-end database file is on a network share. Therefore it is always a good idea to ensure that there are indexes on all fields that participate in WHERE clauses or the ON conditions of JOINs.

Example: I have a sample table with one million rows and a field named [Category] that contains the value 'A' or 'B'. Without an index on the [Category] field the query

SELECT COUNT(*) AS n FROM [TestData] WHERE [Category] = 'B'

had to do a table scan and that generated about 48 MB of total network traffic. Simply adding an index on [Category] reduced the total network traffic to 0.27 MB for the exact same query.

Upvotes: 2

Related Questions