Single Column index or 3 column index in MS Access 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
I'd say it will make only a negligible difference, but that's from the gut, as factors such as the number of data sets, CPU, RAM and HDD/SSD play a role.

However, I would technically tend to go for the three-field solution, as it gives you flexibility.

You can use them more flexibly in queries.

And consider the case that you might need to replace the value of one of the fields with another one later. Then the fun really starts with the one-field solution...