Ms-access – Single Column index or 3 column index in MS Access

database-designindexms 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

Best Answer

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

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

Sql-server – Indexing strategy for dynamic predicate

If I were you I would run a trace specific to hits on that table. It shouldn't be overly intensive since you are restricting it to just queries against that table, from your application. Run just the minimum needed by the DTA (Database Tuning Advisor). Run it for a day here, a day there, make sure you get some end of week days and some end of month days. Then run the whole lot through the DTA.

Here is why, I'm willing to bet that you have specific combinations of columns that are going to come up more often than not. You can create more complex indexes based on that information. You might also find that you can create some correlated statistics. Basically statistics that have more than one column. For example creating a statistic on City and State together may improve queries against those two columns.

However make sure you don't create to many indexes. On a table that large I'm guessing you do a fair number of writes and every additional index added will slow them down. Of course you may do most of your writes during a batch process.

Also make sure that you put an automatic process to update your statistics periodically. With that many rows the statistics aren't going to update on their own very often. Only once 500+20% of the rows have changed, in at 500 mil rows that's a LOT.

Best Answer

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

Sql-server – Indexing strategy for dynamic predicate

Related Question