Table of Contents
BinaryLink M:M vs FK Join Speed
Let's compare:
- a regular Relational Model of M:M link between T1 and T2 with help of the third table T12 and two FK-links T1-T12 and T12-T2. Both links are based on ID and PTR fields of ULONG (4 bytes) type.
- and Valentina Model with two tables T1 and T2 linked using BinaryLink of M:M kind.
And let`s assume that we should do logical join of T1 and T2 records. Note, that logical sense of T12 is only implementation of M:M links between records of T1 and T2.
Foreign Key
To resolve this task, relational DBMS should execute two joins: 1) join of T1.ID to T12.Ptr1. For this indexes of T1.ID and T12.Ptr1 are used. 2) then join table produced on the step 1 should be joined to T2.
So we have two joins, which may use 4 indexes. Actually things are even harder if go deeply.
BinaryLink
With BinaryLink of M:M kind we solve the task accessing effectively a single(!!!) index.
Bench
Simple bench have shown near to x8 faster speed of BinaryLink over MM third table.