Using Access 2000, I have a database with one table which contains about 3 million records and 29 fields. The MDB file is about 1.6 GB without any indexes.

I created a simple query that selects only a Name field and has a Where clause with 3 "Like..OR.." expressions and sorts the results:


SELECT T.Name
FROM Test AS T
WHERE (((T.Name) Like "*stryk*" Or (T.Name) Like "*bost*" Or (T.Name) Like "John*"))
ORDER BY T.Name;

It returns about 35,000 records and takes about 23 seconds to run with and without an index on the Name field.

I thought I would see a time difference using the index.

Question: Is this database too small or is the query too simple to show the worth of indexing.

A "like" can never use an index anyways, no database is "to small" and no query is "to simple" to use indexes....

Hi MailMan,

What you say makes sense. And trying my query without LIKE proves that you are right. The non index version took about 10 seconds and with the index it seemed to be instantaneous.

I then tried the following query with "Group By":
SELECT T.Name, T.[Product Number], Count(*) AS [Count]
FROM Test AS T
GROUP BY T.Name, T.[Product Number]
ORDER BY T.Name, T.[Product Number];


It returned about 57,000 records in about 13 seconds with and without and index.

Question: As with "Like", is "Group By" a situation where indexing does not help?

Group by is a different "animal" than a where clause, totaly uncomparable.

You are doing a query, as with the like, which requires the full table to be retrieved and counted. The like needs to search the full table, a direct search in "fast flick" thru the index and use that to retrieve your data.

When ever a "full table scan" is needed its going to "take for ever", if you can use an index... well your a happy customer :)

Also full scan >> Slow
SELECT T.Name, T.[Product Number], Count(*) AS [Count]
FROM Test AS T
WHERE T.Name Like "*stryk*"
GROUP BY T.Name, T.[Product Number]
ORDER BY T.Name, T.[Product Number];

indexed search >> Much faster
SELECT T.Name, T.[Product Number], Count(*) AS [Count]
FROM Test AS T
WHERE T.Name = "stryk"
GROUP BY T.Name, T.[Product Number]
ORDER BY T.Name, T.[Product Number];

<Broken record mode>
Please dont use spaces or special characters (_)(*&^%$#@! etc) in table/column names
and please use a naming convention simular to tblName, qryName, frmName for tables/query/forms respectively (and other objects simular prefixes) , you will love yourself for doing it
</Broken record mode>