Single Index or Multiple Column Index (1 Viewer)

[BiigJiim]

Hi,

I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated.

There will be a single NO DUPLICATES index on either the 3 fields or the single concatenated field.

My question is this: from a performance point of view, is it faster to have a single text field with a single column index, or a 3 column index covering the 3 text fields? My guess is the single option, but I don't know how much difference it makes? There will be approx 20 possible table names, 100 possible field names, and a few thousand caseIDs.

I only need to search on a combination of the 3 text fields, never on any of the fields individually.

Thanks for any advice!
Jim

 

[namliam]

no difference that you will notice. thus go for the best solution and split the columns.

 

[jdraw]

With so few anticipated records I don't think you would notice any performance differences.
I'd be a little concerned with the memo field and how it is used. And more concerned with "never".
I've seen a few instances of never coming back to bite well-intended designers.
Anyway good luck with your project.

 

[BiigJiim]

Thanks for the feedback guys. I agree with the never comment - but in this case I am adding a one-off solution to mitigate a poorly designed db in the first place, so this is a real 'never'

Going with 3 fields and a single index for all 3.

 

[Micron]

So 3 fields will make up a composite index? Do any of the fields contain null values, and are you going to be adding records that might contain null in any of those 3 fields?

 

[BiigJiim]

So 3 fields will make up a composite index? Do any of the fields contain null values, and are you going to be adding records that might contain null in any of those 3 fields?

No, all of the fields will always contain a value.

3 field composite index seems to work quickly.

 

[Micron]

OK, because I found the details when creating such indexes to be a bit misleading, or at least I misinterpreted the information. In such an index nulls allow duplicates in the other fields:
my | pet | cat
my | pet | dog
my | pet | fish
my | pet |
my | pet |
where the 2 missing values are null.

Corrected an entry.

 

[zeroaccess]

I found this to be very helpful: