-1

Background

I have a "join" table that is used to join one "transactional" table with a "category" table based on multiple columns (and through several joins):

TxnTable(Entity1Name, Entity2Name, Entity3Name)

EntityTable x3 (Id, Name)

JoinTable(Entity1Id, Entity2Id, Entity3Id, CategoryId)

CategoryTable(Id, Name, etc)

The standard query is basically:

SELECT txn.*, cat.Name
FROM TxnTable txn

-- Go from transaction names to IDs
INNER JOIN Entity1 e1 ON e1.Name = txn.Entity1Name
INNER JOIN Entity2 e2 ON e2.Name = txn.Entity2Name
INNER JOIN Entity3 e3 ON e3.Name = txn.Entity3Name

-- Lookup category from join/mapping table
INNER JOIN JoinTable jt
  ON jt.Entity1Id = e1.Id
  AND jt.Entity2Id = e2.Id
  AND jt.Entity3Id = e3.Id

-- Load Category information
INNER JOIN CategoryTable cat ON cat.Id = jt.CategoryId

Question

When designing indexes for performance purposes for the "Join" table, should I create four individual indexes:

CREATE INDEX IX_1 ON JoinTable (Entity1Id)
CREATE INDEX IX_2 ON JoinTable (Entity2Id)
CREATE INDEX IX_3 ON JoinTable (Entity3Id)
CREATE INDEX IX_4 ON JoinTable (CategoryId)

or one combined index

CREATE INDEX IX_Combined ON JoinTable (Entity1Id, Entity2Id, Entity3Id, CategoryId)

or some other combination?

  • 1
    What results did you obtain when testing with the index configurations you mention? Was the data load representative when you tested? – MDCCL Oct 23 '18 at 20:59
1

Based on your sample query, you would want a combined index,

Reasoning:

Although one Txn record joins to three different Entity# tables, once those joins are completed, that can be viewed for efficiency purposes as a single record containing all of the Txn data + the Entity#IDs. If you had a single record that contained that data, you would want to join on one index instead of three separate indexes to find the row(s) in the Join table that you were looking for. That same efficiency argument applies here.

Having the CategoryId as part of the Join table index is optional, but it is more efficient for the next join to the Category table, since it is only concerned with the data CategoryId, not which row it is attached to in the Join table.

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.