MS Access Index - Sequencing and Multi

i have an access database (moving to a different database is not an option) and i am looking to change/enhance the indexing to speed up searches.

a basic search always includes a code specific to the person/group doing the search.  within that code, searches are done on 3 or 4 other fields (bill number, registration, last name, first name).  question is how should the indexing exist?

if the code is part of every search, should i be creating multi-field indexes with the code in each?  such as :

code + bill numb
code + registration

or, should they all be separate indexes such as
bill numb
lastname + firstname

hope that makes sense?  

lastly, does the order of the fields in the WHERE portion of a select statement help speed w/r/t to the indexes?

thank you in advance.


code + bill number as a multi, or should code be a separate index, then bill
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Separate indexes should be enough for the basic speed optimization. In short: Access will create bitmaps based on the parsed WHERE clause and then selects the result set of records by combining these bitmaps. (One bit represents one row at output.)

The shorter is the first bitmap created the better is the result retrieval speed... But who knows how (or even if) Access optimizes these bitmaps order creation. You may try to swap  particular parts of your WHERE clause and measure the result time.

The index contribution to the query speed is roughly given by the number of rows which it can select or filter out. In other words if you have column having just two possible values in 50:50 ratio then the index is not necessary because other indexes will probably do better result rows selection.

I don't understand one part of your question:
1) The code represents the person/group doing the search
2) Other columns do not represent this person
Do you have all above values in one table? They seem to be unrelated.

I suppose we are talking about more tables in your database. And more tables should also have indexes on columns used for these tables connection (relation). If you would show your SQL command then it would be easier to say what indexes could help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deputycollectorAuthor Commented:
Thank you for the quick response and education.  I’ll start testing that concept tonight.

The data is basically in one giant table.  Any link/join to other tables are done with the primary key (below) and the other tables are basically look-up in nature and/or payments….but those don’t really factor into my speed issue just yet as the first step of the application’s search is hitting this table.  Table has about 500,000 records and is not normalized to the extent it should be…but, that’s a bit further down the line…stopping the bleeding is the first step.

Below is a quick visual of how the code field works in this database.  The table has 93 fields.

billkey : compcode      : billnumber : regnumb : name1_l : name1_f : Charges, other fields..
-10002 : 33  : 1 : ZZZYYY23 : Johnson : Chris      
-20000 : 33 : 456 : XXXAA : Johnson : Charles      
10000 : 20 : 1 : XX : Johnson : Chris      
4555 : 20 : 1112 : 99DDF : Smith : Dave      

So, basically, the compcode is tied to users using the system and they really only see and manage their data.  The other data in the row can be the same as the another compcode, but, the information is owned and managed separately.

The billkey is an autonumber (random) and there is a unique key set to “compcode + billnumber”.

The basic search sql would be

SELECT * FROM qry_bills WHERE compcode = 33 AND billnumber = 1;
SELECT * FROM qry_bills WHERE compcode = 33 AND name1_l Like ‘smith%’;
SELECT * FROM qry_bills WHERE compcode = 33 AND name1_l Like ‘smith%’ and name1_f like ‘ch%’;


The bulk of the queries are from a linux web server with a perl front end using an odbc gateway from….if you have performance settings for the odbc driver/listener those would help too!

The company suffers from a major “the way we have always done it” and “firefighting” approach to problems for the last 15 years.  A complete new database design and front end are to be done in the not too distant future.  So, my current issue of extracting as much speed from the existing architecture is first priority.

Any help greatly appreciated.

Thank you

first of all, it is really no good solution to use Access as a backend database for a web frontend. To mention only one important reason: Access has no security model beginning with A2007 anymore. Another thing is that multi-user-access to the database is possible, but as the backend is no active database server but only a passive file the frontend (in your case the driver/webserver) needs to do all the heavy work. Although the backend can save the queries it will not execute them and so this is the first big bottleneck. The webserver would need to work with the data using the JET/ACE engine.
So "not an option" is a choice you should really think about.

Indexing is one of the most important things to get a performant result from a query - but indexing all fields (together or single), especially with such many fields as in your case, is not a good idea. Every record which will be changed forces the database to change all the indexes and especially with a passive file database this means that this work must be done again by the frontend. In a real database server this is a job which runs in the background parallel to other tasks, so the webserver and frontend does not have anything to do with it.

Regarding your example queries you do not have many fields in a search, so a good strategy in indexing is to add indexes to fields which are most important in a search. Because if you search for i.e. compcode and then any other field the index on compcode would restrict your records to the records with this code and so only the remaining records must be scanned for (maybe not indexed) other fields.
If you have a multi-field-index this would be even easier. The order of the fields in the WHERE clause doesn't matter because all criteria must be matched and so the engine decides which index to be used and so it can use the criterion which matches the first field in the index first. Of course it makes sense to create such index in the order from the most common field to the most special field. Like with an address: If you search for a street and know the city and the country you would not start to search a list of all streets first to then search through all cities in the remaining result and then the country - you would do it the other way round, first look for the country, then the city and then the street - and that's the way a multi-field-index should be created. And as I said: Only the most important fields, not simply all.
If you have an index on each field only then each index must be scanned for each criterion in your WHERE clause.

Next thing is that you use "SELECT *". If you use only the fields which are really needed in your frontend the query would of course a lot faster because you would not load all data but only the columns really needed.

The kind of search is also important for speed, for example, a  LIKE "name*" can use an index on this field, a LIKE "*name*" not. This is only one example, there are a lot rules in forming an index-friendly WHERE which you can easily find if you search a little bit on your favourite search engine.
By the way, why do you use "%" as joker? Access usually uses "*", "%" is used by SQL Server for example.

A database server would also cache searches, so if the user retries the same search in the same session a database server would return the cached data instead of executing the same query again.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple general comments specific to JET/ACE based DB's:

1. In general, your better off with individual indexes rather than compound ones.

2. Compound indexes can speed up things quite a bit, but in order to be used, the keys your searching on must be in the exact order of the index for it to be used.

3. Don't index fields with low cardinality  (those with a limited range of values, such as a yes / no field).

4. You can tell if an index is being used or not with JET SHOWPLAN.  This gives the costing plan for query execution in a text file.   It's somewhat limited (doesn't do sub-queries), but it often gives some insights on what JET is trying to do.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.