Index type and Speed in Access

[Charles]

Hello

Would anyone have some light on this question: how critical is the
type of the field used as an index for a large database.

Let's say I have 1 million entries, every entry has a field called
"Reference" which contains a 10-11 characters string. This string is
not unique and I sometimes want to select all entries with a specific
reference out of the 1 million entries. Reference is naturally
indexed.

How faster do you think it would be to have another table mapping
every reference to a "Reference ID" which would be an integer, and to
have in the 1m table not the Reference but a Reference ID. In other
worlds, how faster is it to run a query on an indexed string (SELECT
FROM WHERE Reference="UEJ22554EE") and an indexed integer (SELECT FROM
WHERE ReferenceID=15455) on a massive 1m table.

Is it worth the pain of having extra tables?

Thanks in advance
Charles

 

[John W. Vinson]

Hello

Would anyone have some light on this question: how critical is the
type of the field used as an index for a large database.

Let's say I have 1 million entries, every entry has a field called
"Reference" which contains a 10-11 characters string. This string is
not unique and I sometimes want to select all entries with a specific
reference out of the 1 million entries. Reference is naturally
indexed.

How faster do you think it would be to have another table mapping
every reference to a "Reference ID" which would be an integer, and to
have in the 1m table not the Reference but a Reference ID. In other
worlds, how faster is it to run a query on an indexed string (SELECT
FROM WHERE Reference="UEJ22554EE") and an indexed integer (SELECT FROM
WHERE ReferenceID=15455) on a massive 1m table.

Is it worth the pain of having extra tables?

Thanks in advance
Charles

If you're willing to give it a try and report your results, there are quite a
few folks who would be interested.

I would GUESS - on insufficient evidence - that you would lose more by having
two tables and two indexes than you would gain by having the slightly smaller
index.

 

[Charles]

To be honest I went directly to an intermediary solution: convert all
strings to long using a method that shouldn't be too destructive and
store both, but only index long data.

I ran the comparison in VBA, by running a comparison between several
hundred thousands strings and the same with long variables. Using long
is about 10 times faster. I imagine Access is more sophisticated than
simply going through the entire list one by one but I would be
suprised if the difference was unsignificant

Charles