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

  • So will you effectively have 20 * 100 * 1000 * x = 4 million records or more? Or are there much fewer combinations?
    – Andre
    Feb 19 '20 at 14:49
  • Generally, if you want an answer to such a specific question, don't ask but benchmark! I can guess, but a guess is rarely as accurate as a measurement.
    – Erik A
    Feb 19 '20 at 15:08

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...

  • Thanks guys, going with the 3 fields, one index solution...
    – BiigJiim
    Feb 19 '20 at 19:17
  • @BiigJiim: You're welcome. Please consider accepting the answer if you find that it solved your problem. Feb 19 '20 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.